Trying to connect to Access Database

[hagwag]

Let>connstr=Driver={Microsoft Access Driver (*.mdb)};Dbq=k:\Write to GS.mdb;Uid=Admin;Pwd=;
DBConnect>connstr,dbH
Let>SQL=SELECT Job FROM Write to GS
DBQuery>dbH,SQL,rsPart,numRecs,numFields
DBClose>dbH

I'm trying to write a macro to return fields from an Access table. I've done this with another SQL application. I went to http://www.connectionstrings.com and pulled this connection string. I'm new to SQL, I'd say my biggest hangup is determining if the connection string is correct.

The table I am pulling from has 12 columns and 5 rows. It's just a sample for now. The columns are Job, Suffix, Customer, Customer Num, PartName,PartData2, Sheet Width, Sheet Length, Issue, PartData4, DATE.
The table is Write to GS. The database is called k:\Write to GS.mdb.

The K drive is another computer. It is a shared folder.

Any help would be great. My biggest hangup is understanding connection strings. I've read a lot about them, but still do not know how to test the connection.

[Marcus Tettmar]

1) Testing the connection. Use the debugger. Step past the DBConnect line and look at the value of dbH. If successful you should see this set to 1 (assuming this is your first/only connect in the script). If you had the connection string wrong you'd most likely get a pop up error depending on the ODBC driver. In the case of Access if the file wasn't found it would pop up an error.

But I can tell you your connection string is CORRECT assuming k:\write to gs.mdb exists and can be opened.

2) Your problem is the fact you have spaces in your table name. Access lets you do stupid things like this. Luckily Access gives you a way to get round it, by putting the table name in square brackets:

Let>SQL=SELECT Job From [Write to GS]

So change your SQL line to that and you should find it works. Of course you won't SEE your data if you just run your script because your script doesn't display anything or do anything with it. But step through with the debugger and you'll now see the array returned and the value of numFields.

[hagwag]

Thank you! That worked. The dbH=1. That I did not know. That will help a lot. Thanks again!

[hagwag]

I have two different DBExec that I am running. I wrote the first one. It writes to an Access DB. It is successful, and the result=1. I wrote the second DBExec. It is writing to Pervasive. It is also successful, and the result=0. Can someone explain the result value?

[Marcus Tettmar]

DBExec should return the number of rows affected by the query. Can you share your code?