ADO Database Name Syntax

[scott]

In my code I'm trying to "import" data from table2, which is an external
dBASE file into table1 which resides within Access.When I run my code,
Access doesn't recognize table1 because I've changed the connection for the
dBASE sql part.

How can I modify my code so Access knows that table1 resides within Access?


CODE **************

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sPath As String, sFile As String, sSQL As String

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

path= "C:\data\mydatabases\"
sFile = "myDBASEfile.DBF"

sSQL = "INSERT INTO table1(myDate, myData1) " & _
"SELECT myDate, myData1 " & _
"FROM table2 " & _
"WHERE myDate NOT IN (SELECT myDate FROM table2)"

cnn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & _
"DriverID=277;" & _
"Dbq=" & sPath

rst.Open sSQL, cnn, , , adCmdText

 

[Douglas J. Steele]

You can't. An ADO connection can only connect to a single data source.

You could try creating a linked table pointing to your dBase table and then
use the linked table and the actual Jet table in the same statement.

 

[scott]

My problem with that method is my dbase file always changes names with the
day's date. I'm looking for syntax to create a dbase linked server with
code, please send any links if you have any on that topic.

 

[Douglas J. Steele]

I believe it's something like:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.CreateTableDef("LinkedTable")
tdfCurr.Connect = "dBase IV;DATABASE=C:\dBASE\Samples"
tdfCurr.SourceTableName = "Accounts"
dbCurr.TableDefs.Append tdfCurr

If the dBase file is always in the same folder, you might be able to get
away with simply changing the SourceTableName property of the table, and not
constantly delete the existing table and create a new one.

 

[scott]

1. I'm getting an "object variable or with block variable not set" error on
the line below:

Set tdfCurr = dbCurr.CreateTableDef("LinkedTable")

2. In your example code, what will the Linked Server Name be?

3. Given that this code will run from a local workstation and the sql server
is on the LAN server, won't there be a "relative path" problem with the
actual path to the DBF file?


note: I'm using the MS 3.51 DAO library.

 

[scott]

One other thing, this is an ADP Access file, so I don't think your example
will work.

I'm trying to find a way either through code or manually to create a "Linked
Server" on a SQL Server.

 

[Douglas J. Steele]

You didn't mention ADP! I don't believe it will work.

Afraid I don't know what options, if any, you have.