TransferDatabase (ODBC DSN Errors)

[Mogsey]

Dear All,

I have what is likely to be a very small problem for an Access guru, connecting to an ODBC database in VBA in Access 2002. I recieve an error code of 3151 ODBC Connection to 'C:\TempDSN.dsn' Failed.

The code I am using is as follows: -

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=C:\TempDSN.dsn;" _
& "DATABASE=" & CurrentDb.Name, acTable, "NODEGEOMETRY"

i have a procedure that searches for applicable databases in a given directory and its sub folders, creates the DSN file, then using the newly created DSN, creates a recordset of the required data and fires it into Access. When I use ->file ->get external data -> import, then select ODBC the data comes into access in about five minutes. The trouble is programmatically it takes upto an hour. One Table has 100,000's of records. (Will eventually load data into SQL)

I want to be able to pull everything in as the menu commands does, but for all files in a folder, inc sub folders, hence the need for TransferDatabase to work from within VBA.

Can anyone help with this issue or know of a more efficient method to import the data.

Thanks muchly in advance

Mogsey

[MMcCarthy]

ODBC connections don't work based on file path but just the DSN name.

DoCmd.TransferDatabase acImport, "ODBC Database", _
"ODBC;DSN=TempDSN;" _
& "DATABASE=" & CurrentDb.Name, acTable, "NODEGEOMETRY"

The code for an odbc connection is as follows:

ODBC;DSN=TempDSN;APP=Microsoft® Access;DATABASE=DatabaseName;TABLE=TableName

[Mogsey]

Hi M,

I'm still getting a 3151 error, ODBC-Connection to 'Temp.DSN' failed.

Do i have to register the dsn anywhere?

Thanks

Mogsey

[MMcCarthy]

Hi M,

I'm still getting a 3151 error, ODBC-Connection to 'Temp.DSN' failed.

Do i have to register the dsn anywhere?

Thanks

Mogsey

You need to create the DSN connection and it's not Temp.DSN. I got the name TempDSN from your code.

The easiest way to create the DSN connection is to open access and go to Get External Data - Link files. This will open a dialog window. Change the filetype to odbc databases and this will open the connection window. You can create a new DSN connection from here.

Mary