Wiki Home

Remote Accessto MSAccess Database


Namespace: WIN_COM_API

I want to be able to connect to an Access database(insert, delete updated records) located on a remote server from within Foxpro. How do I go about doing this

Start with this:

HOWTO: Use Microsoft Access Data in Visual FoxPro
http://support.microsoft.com/default.aspx?scid=kb;en-us;133286
I don't recommend anyone use this method unless you only need to connect to a single Access MDB file.

In my case, I needed to mass convert several hundred Access97 MDB files to SQL Server 2008. I tried using the remote view method by programatically creating remote views on-the-fly while navigating through a directory structure. VFP9 works very well for this type of problem. The show-stopper manifested itself when I attempted to create the remote view, then USE the view, I would get a 'Select Database' prompt for each action. Two prompts for every MDB I tried to connect to. Not fun...not a program one can fully automate.
I eventually switched to SQL String Connect, SQLExec, SQLDisconnect commands and the clouds parted, the sun came out and all is well in River City.
In summary, if you need to move data to and from Access MDBs, at least the older versions, use the second method, not remote views.
Also, remote views wouldn't support Insert Into syntax for some reason. I didn't play with it for long before I switched to Scatter/Append/Gather code. No problems that way.

Jeremy Lanctot
( Topic last updated: 2011.05.24 11:47:55 AM )