Programming Forums - How to connect with VB to an Access database on another computer

Programming Forums (http://www.programmingforums.org/forumindex.php)

- Visual Basic (http://www.programmingforums.org/forum18.html)

- - How to connect with VB to an Access database on another computer (http://www.programmingforums.org/showthread.php?t=5233)

How to connect with VB to an Access database on another computer

Hi,
I'm trying to write just a simple VB application that runs on machine X and accesses an Access table on machine Z. I don't really want to map any drives or such thing. Machine X and Z are in the same network so they can see each other. Does anyone know what the connection string would be for the application to access the table?

Let's say that I want to retrieve all records whose customers whose first name is = "mark". The basic structure to open a db is...

Set dbsCurrent = OpenDatabase("xxxx")

... and then what??? what is the next thing that I have to do to get all the records from machine Z to machine X? What is the exact connection string that I have to use in order to access an Access database over the network?

Thank you in advance.

i could be wrong but i think you need a service to run on box Z to serve the db up.

Why are you using access? there are far better db's out there (that are commercial quality) that are free (read: free as in beer). Like Postgresql and MySQL

I like postgresql myself, but many others will tell you mysql. VB will connect to these two no problem.

If you still want to use a access db, you'll need box Z to run IIS (or personal web server), and then have a DSN pointing to your db. Then connect with something like this:

Dim cnnConnection as Connection



set cnnConnection = new Connection



cnnConnection.Open "Provider=MS Remote;" _

        & "Remote Server=http://www.myserver.com;" _

        & "Data Source=MyDataSource;" _

        & "User Id=MyUserName;Password=MyPassword;"

Then all you'd have to do is run a query, and output the data or do what ever you need to do with it.

Note: You do not need to put a web address into the Remote Server Part of the connection strings, you could use something like http://192.168.0.11 or what ever the internal ip of box Z is.

Also i have no idea if you can use apache (a free and better web server IMHO) for this or not, so your on your own for that part :-)

you could also map a network drive where the DB is located

yeah i don't think he really wants to do that though. but it would be the easyest way to do it.

It probably would work, but the Access DBM isn't truly multiuse (it queues requests) and has a maximum of 10 "simultaneous" users (instances of the application). Plus it's Access. Urghh! :)

Yeah, which is why i suggested Postgresql or MySQL.