dsnless connection to access table 1

[robsuttonjr]

What is the most reliable and secure way to pull data from access tables without having to setup a DSN? I have to pull from a lame program (Blue Pumpkin) which creates a table for every week and names the .mdb by the week, for example, ch9U016.mdb is for call history for sept 30, 2001, group 6. I need to be able to use some scheme to pull data based on a user picking the week. My problem is that I cannot just setup a single DSN to the data since the table name is variable. I will figure out how to find the database file name but how do I pull data from it without having a DSN?

 

[robsuttonjr]

I did find the Faq on DSNLESS connections. But I want to know still how reliable is this and does anybody every have problems with locking up files via DSNLESS connections? The software that generates the .mdb files is very tempermental. I don't want to use an approach that might cause the main program to crash if I am using back door approches that might lock up the files but leaving phantom connections to data etc.

 

[robsuttonjr]

Now I have tried the DSNless approach but I get a dialog box to choose dsn when I use the following:
lnConnHandle=SQLSTRINGCONNECT("DRIVER = Microsoft Access Driver;DATABASE=c:\history\ch9U011.MDB&quot

I just want to dynamically connect to the remote .mdb without having any dialog boxes come up. Any ideas?

 

[arunrote]

I written this below code for accessing foxpro database without dsn.I think it may help u.

con=createobject('adodb.connection')
con.OPEN("DRIVER=Microsoft Visual FoxPro Driver;" + ;
"SourceType=DBF;SourceDB="+SYS(5)+curdir() +";" + ;
"Exclusive=NO;BackGroundFetch=NO;NULL=NO;Collate=MACHINE;" + ;
"OLE DB Services = -4&quot

 

[jonscott8]

You didnt specify the version of Access, so I'm presuming 2000.

First, SQLSTRINGCONNECT doesnt like spaces after the DRIVE clause, which is contradictory because most of the examples in the documentation include the spaces.
Next, you didnt specify the name of the Access ODBC driver correctly.
Also, Access uses the DBQ clause to specify the database, not DATABASE.

Change your conn. string to the following and try again:
lnConnHandle=SQLSTRINGCONNECT("DRIVER=Microsoft Access Driver (*.mdb);DBQ=c:\history\ch9U011.MDB&quot

If by chance, the database is password protected, use the UID and PWD arguments to represent the username and password respectively. Jon Hawkins

 

[jonscott8]

The following KB article might help:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q193332

Jon Hawkins