Need a code to Import a Database

[joopdog]

I need a code in Access to Import an ODBC Database. I want to write a macro or module to import data into a table using code. I know there's a simple code to import a file or database. I just don't know where to start.

I can manually import the data into a table.

I do this by the following:

1. Right-clicking in the table area
2. Left click on [B]Import[/B]
3. I look for ODBC databases and choose the DSN name [B]prodlgo[/B]
4. It’ll ask for username and password
5. Next it’ll bring up [B]Import Object[/B]. I then select the database to import.
6. The whole database is imported in seconds. DONE!

I need to be able to do the above steps in a macro or programming code. I did this in excel using Record A Macro, however, some of the databases are too big to import with over 200,000 records. Excel can only hold 65,536.

Here’s my Connection String for Access (It Works):

ODBC;DSN=prodlgo;ServerName=192.168.1.2.1583;
ServerDSN=prodlgo;UID=Sharon;PWD=qaz890;
ArrayFetchOn=1;ArrayBufferSize=8;DBQ=.;;
TABLE=CUSTOMER;"

Any help would be appreciated

 

[Robert88]

Hi joopdog,

What type of database is importing?

I recently used a connection like this for a fox pro table. http://www.access-programmers.co.uk/forums/showthread.php?t=102185&highlight=fox+pro

Robert88

 

[joopdog]

I've tried the Code. It looks like it's going work.

DoCmd.TransferDatabase , "ODBC Database", "ODBC;DSN=prodlgo;ServerName=192.168.1.2.1583;ServerDSN=prodlgo;UID=Sharon;PWD=qaz890;ArrayFetchOn=1;ArrayBufferSize=8;DBQ=.;;TABLE=CUSTOMER;", acTable, "test", "tblTest_dbf"

However, I get a run-time 3011 error message. See below:


I'm so close.

The database is located in an UNIX server, so it is an ODBC database. There's many, many databases on this UNIX server. The Import connection string I use is below: This works. We use it to Link the database.

ODBC;DSN=prodlgo;ServerName=192.168.1.2.1583;
ServerDSN=prodlgo;UID=Sharon;PWD=qaz890;
ArrayFetchOn=1;ArrayBufferSize=8;DBQ=.;;
TABLE=CUSTOMER;"

We use Simba Client driver to extract the database into Microsoft Access. I can import the database with no problem. I just a need an Access VBA code to do it.

Any help would be appreciated.

 

[joopdog]

I got it!

I figured it out:

Private Sub CmdReadDBF_Click()
  ' To Import the Database
  DoCmd.TransferDatabase acImport, _
      "ODBC Database", _
      "ODBC;DSN=prodlgo;ServerName=192.168.1.2.1583;ServerDSN=prodlgo;UID=Sharon;PWD=qaz890;ArrayFetchOn=1;ArrayBufferSize=8", _
      acTable, "CUSTOMER", "tblTest_dbf"
  MsgBox "File transfer to tblTest_dbf should be complete? (Cross our fingers)."
End Sub

DoCmd.TransferDatabase
Transfer Type: acImport
Database Type: "ODBC Database"
Database Name: Database Source (The UNIX server conncetion string)
Object Type : acTable
Source : the name of the database in UNIX (It's case-sensitive. This caused the above run-time error 3011)
Destination : What table name in Acccess you want to import the database file in.
StructureType: Default is False. Copies the field names and data. True, copies onl.y the field names.

Hope this help anyone else.

 

[Robert88]

Well Done

Sorry for not getting back earlier, well done!