ODBC connection failed - MS Access to DBF

[SnyAc]

I'm trying to convert some Access databases to VFP using the following code... and I get an 'ODBC connection failed' message. Where do you get the ODBC drivers?

lcMyFile = iif(pcount()=1,cMdbPath,GETFILE('MDB'))
lnResult=0
*!* No point if no MDB file is selected
IF !EMPTY(lcMyFile)
   *!* Use a SQLStringConnect to connect to the MDB file
   lnConnHandle = ;
      SQLSTRINGCONNECT('DRIVER=MICROSOFT ACCESS DRIVER (*.MDB);DBQ=' + ;
         lcMyFile)

Andy Snyder
SnyAc Software Services

http://www.snysoft.com

 

[bborissov]

Try this:

lcMyFile = iif(pcount()=1,cMdbPath,GETFILE('MDB'))
lnResult=0
*!* No point if no MDB file is selected
IF !EMPTY(lcMyFile)
   *!* Use a SQLStringConnect to connect to the MDB file
   lnConnHandle = ;
      SQLSTRINGCONNECT( "Driver={Microsoft Access Driver (*.mdb)};Dbq=" + lcMyFile + ";Uid=Admin;Pwd=;")

Borislav Borissov

 

[SnyAc]

Borislav.... thanks for the suggestion. I still get an error tho...
The error is "1526 - Connectivity error: Unable to retrieve specific error information. Driver is probably out of resources."




Andy Snyder
SnyAc Software Services

http://www.snysoft.com

 

[Mike Lewis]

Andy,

Try creating a DSN interactively in the ODBC control panel applet. You don't actually have to use the DSN. Just see if you can create it. If you can, then it means your syntax is at fault. If you can't, that would suggest a problem with the driver.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[SnyAc]

Mike.... Sorry but I don't know how to do that... I don't use ODBC resources in my apps. I'm only using it here because I'm trying to import a potential customer's data from another app written in Access to my VFP app.



Andy Snyder
SnyAc Software Services

http://www.snysoft.com

 

[alvechurchdata]

I'm trying to import a potential customer's data from another app written in Access

If this is a one-time requirement you could go into Access and push the data out as a dbf. It'll be a Fox2 table so you'll lose any long field names but correcting the names might be easier than fighting ODBC.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Mike Lewis]

Aandy,

Sorry but I don't know how to do that[/quote}

1. Go to Control Panel / Admninstrative Tools / Data Sources.

2. Click Add.

3. Select the Microsoft Access driver. Click Next.

4. You are now in the Access setup dialogue. Enter a name for your data source (say, Test). Click Select, and select your MDB file. Click OK.

5. Back in VFP, change your code so that, instead of SQLSTRINGCONNECT(), you have SQLCONNECT(), passing the name you specified in step 4 (Test, or whatever).

If that works (no error message, and SQLCONNECT() returns a positive number), then your ODBC driver is OK, and there was probably something wrong with the syntax of your original connection string.

If that's the case, you could always consider making the above changes permanently.

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[SnyAc]

Mike .... I created a data source named 'test' as per your instructions... I then tried the sqlconnect from the command window.

ln=sqlconnect("Driver={test};dbq=-c:\The System Source\CashWare\CashWare.mdb;Uid=Admin;Pwd=;")
? ln

It returned -1.

I'm using the code snippet from microsoft to do the odbc connection. This worked a couple of weeks ago on a test run on my main server, but wouldn't work on my laptop. I recently upgraded my server and hard drive which required a clean/new install of XP. Now this code doesn't work on the server either. My conclusion therefore is that the drivers must not be installed or not registered correctly. All of my computers have OfficeXP installed as well so Access has been installed, though I don't want that to be a requirement for this bit of code to work.

Always something isn't it...



Andy Snyder
SnyAc Software Services

http://www.snysoft.com

 

[Mike Lewis]

Andy,

Sorry not to have replied sooner. It's been a busy week.

Unfortunately, your code is wrong. You shouldn't be passing the entire connection string to SQLCONNECT(). Just pass the name of your data source (test, in your case):

ln = SQLCONNECT("Test")

The fact that you were able to create the data source suggests that the driver is OK. If the above doesn't work, there is some other problem.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[baltman]

See if Import DB from MS Access faq1251-5002 helps. Be aware that numers with > 2 decimals usually get rounded to 2 decimals during any kind of export from Access (other than export to excel).

Brian

 

[TamarGranor]

Rather than having to set up a connection at the Windows level, you can pass the entire connection string to SQLStringConnect().

Tamar

 

[Mike Lewis]

Tamar,

Rather than having to set up a connection at the Windows level, you can pass the entire connection string to SQLStringConnect().

That's what Andy was trying to do in the first place. My suggestion was to see if he could create the DSN in the control panel applet. That would confirm or otherwise that the Access driver was working OK, which was his original concern.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)