471,337 Members | 802 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,337 software developers and data experts.

TransferDatabase (ODBC DSN Errors)

2
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
Apr 2 '07 #1
3 5342
MMcCarthy
14,534 Expert Mod 8TB
ODBC connections don't work based on file path but just the DSN name.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.TransferDatabase acImport, "ODBC Database", _
  2. "ODBC;DSN=TempDSN;" _
  3. & "DATABASE=" & CurrentDb.Name, acTable, "NODEGEOMETRY"
The code for an odbc connection is as follows:
Expand|Select|Wrap|Line Numbers
  1. ODBC;DSN=TempDSN;APP=Microsoft® Access;DATABASE=DatabaseName;TABLE=TableName
Apr 2 '07 #2
Mogsey
2
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
Apr 4 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
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
Apr 4 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Steve | last post: by
2 posts views Thread by Pachydermitis | last post: by
1 post views Thread by Danny J. Lesandrini | last post: by
4 posts views Thread by Phil Latio | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.