VBA To Import Tables

[eklahorst via AccessMonster.com]

I have a project that I have been assigned where I need to import about 100
tables from an ODBC database daily, run a query on these tables, and then
delete the table. The table names are all located in a table on the database
titled Catalog Update and the column/field is title Tables. I am pretty new
to VBA and I am wondering how to set up a module that would import all of the
tables based upon what is stored on the Catalog Update Table, I already have
something that will run the query and delete the table, I am just stuck on
the import section. Any suggestions?

 

[pietlinden]

I have a project that I have been assigned where I need to import about 100
tables from an ODBC database daily, run a query on these tables, and then
delete the table.  The table names are all located in a table on the database
titled Catalog Update and the column/field is title Tables.  I am pretty new
to VBA and I am wondering how to set up a module that would import all ofthe
tables based upon what is stored on the Catalog Update Table, I already have
something that will run the query and delete the table, I am just stuck on
the import section.  Any suggestions?

Something like this ought to work... The basic idea is to specify the
database you want to import objects from, and then loop through the
table containing the object names to import. In my case it's the
field called "Foreign Table Name" (Yes, I know, I shouldn't have
spaces in my field names... it's for clarity!)

If you don't need to import the actual tables, but only the data in
them, you could use acLink instead of acImport. (Or if you set up the
links and the table names are all the same, you could just modify
the .Connect property of the tables you process each time.

Public Sub ImportListOfTables(ByVal strFullPathToDB As String)
Dim rsT As DAO.Recordset
Set rsT = DBEngine(0)(0).OpenRecordset("tblListOfTables",
dbForwardOnly)
Do Until rsT.EOF
DoCmd.TransferDatabase acImport, "Microsoft Access", _
strFullPathToDB, acTable, rsT.Fields("Foreign Table Name"), _
"Local Table Name"
rsT.MoveNext
Loop
rsT.Close
Set rsT = Nothing
End Sub

In that case, you'd have
Do Until rsT.EOF
DBEngine(0)(0).Tabledefs("strTable").Connect=strFullPathToDB
rsT.MoveNext
loop

 

[RD]

I have a project that I have been assigned where I need to import about 100
tables from an ODBC database daily, run a query on these tables, and then
delete the table. The table names are all located in a table on the database
titled Catalog Update and the column/field is title Tables. I am pretty new
to VBA and I am wondering how to set up a module that would import all of the
tables based upon what is stored on the Catalog Update Table, I already have
something that will run the query and delete the table, I am just stuck on
the import section. Any suggestions?

Suggestion? I suggest that you don't import, query and delete. It's
an ODBC database. Just link the tables and query them without the
importing and deleting.

 

[eklahorst via AccessMonster.com]

I have a project that I have been assigned where I need to import about 100
tables from an ODBC database daily, run a query on these tables, and then

[quoted text clipped - 4 lines]

something that will run the query and delete the table, I am just stuck on
the import section. Any suggestions?

Suggestion? I suggest that you don't import, query and delete. It's
an ODBC database. Just link the tables and query them without the
importing and deleting.

The reason that I am importing and deleting is because the table names are
variables and not always the same.

 

[Douglas J. Steele]

I have a project that I have been assigned where I need to import about
100
tables from an ODBC database daily, run a query on these tables, and then

[quoted text clipped - 4 lines]

something that will run the query and delete the table, I am just stuck
on
the import section. Any suggestions?

Suggestion? I suggest that you don't import, query and delete. It's
an ODBC database. Just link the tables and query them without the
importing and deleting.

The reason that I am importing and deleting is because the table names are
variables and not always the same.

Why not generate the SQL for the queries on the fly?

 

[eklahorst via AccessMonster.com]

Where I am stuck is the importing of the files, I need to grab the detail
listing on the tables.

[quoted text clipped - 10 lines]

The reason that I am importing and deleting is because the table names are
variables and not always the same.

Why not generate the SQL for the queries on the fly?

 

[RD]

I have a project that I have been assigned where I need to import about 100
tables from an ODBC database daily, run a query on these tables, and then

[quoted text clipped - 4 lines]

something that will run the query and delete the table, I am just stuck on
the import section. Any suggestions?

Suggestion? I suggest that you don't import, query and delete. It's
an ODBC database. Just link the tables and query them without the
importing and deleting.

The reason that I am importing and deleting is because the table names are
variables and not always the same.

Doesn't matter. Below is a function I use to attach text files as
tables. It can be adapted to attach ODBC tables. You can move the
delete part to occur after you run your query. Be careful of the
dreaded line wrap.

Public Function fImportExtract(sExtract As String) As Boolean
On Error GoTo ErrorHandler
Dim db As DAO.Database, tdTblDef As DAO.TableDef
Dim sDbPath As String

' Delete the table if it already exists
If fTableExists(sExtract) Then DoCmd.DeleteObject acTable, sExtract

Set db = CurrentDb
sDbPath = Application.CurrentProject.Path & "\TXT files\"
Set tdTblDef = db.CreateTableDef(sExtract) ' Create the table
tdTblDef.Connect = "TEXT;Database=" & sDbPath & ";table=" & sExtract &
".TXT" ' Connect it to the text file
tdTblDef.SourceTableName = sExtract & ".TXT" ' Name the source file
db.TableDefs.Append tdTblDef ' Append the new table to the table
collection
db.TableDefs.Refresh ' Refresh the table collection

fImportExtract = True

ExitPoint:
On Error Resume Next
Set db = Nothing
Exit Function

ErrorHandler:
If Err.Number = 3051 Then Resume Next
If Err.Number = 3265 Then Resume Next
Debug.Print Err.Number & ": " & Err.Description
Stop
fImportExtract = False
Resume ExitPoint

End Function

 

[eklahorst via AccessMonster.com]

I am sorry, I don't understand. I have a table in my database where the
names of the 100 tables are listed (Catalog Update is the table name). What
I am trying to do is create an import process that will read the table names
from the Catalog Update table.

[quoted text clipped - 9 lines]

The reason that I am importing and deleting is because the table names are
variables and not always the same.

Doesn't matter. Below is a function I use to attach text files as
tables. It can be adapted to attach ODBC tables. You can move the
delete part to occur after you run your query. Be careful of the
dreaded line wrap.

Public Function fImportExtract(sExtract As String) As Boolean
On Error GoTo ErrorHandler
Dim db As DAO.Database, tdTblDef As DAO.TableDef
Dim sDbPath As String

' Delete the table if it already exists
If fTableExists(sExtract) Then DoCmd.DeleteObject acTable, sExtract

Set db = CurrentDb
sDbPath = Application.CurrentProject.Path & "\TXT files\"
Set tdTblDef = db.CreateTableDef(sExtract) ' Create the table
tdTblDef.Connect = "TEXT;Database=" & sDbPath & ";table=" & sExtract &
".TXT" ' Connect it to the text file
tdTblDef.SourceTableName = sExtract & ".TXT" ' Name the source file
db.TableDefs.Append tdTblDef ' Append the new table to the table
collection
db.TableDefs.Refresh ' Refresh the table collection

fImportExtract = True

ExitPoint:
On Error Resume Next
Set db = Nothing
Exit Function

ErrorHandler:
If Err.Number = 3051 Then Resume Next
If Err.Number = 3265 Then Resume Next
Debug.Print Err.Number & ": " & Err.Description
Stop
fImportExtract = False
Resume ExitPoint

End Function

 

[RD]

I am sorry, I don't understand. I have a table in my database where the

names of the 100 tables are listed (Catalog Update is the table name). What
I am trying to do is create an import process that will read the table names
from the Catalog Update table.

You'll need to write a routine that loops through your local table and
then creates links to the remote tables.

I'm assuming you have a DSN for the ODBC database. If not, you'll
need to create one in the ODBC manager.

I just threw the following together while being bored to tears on a
conference call. It's untested and I may have been distracted by the
call but it should be close. Substitute your real values for the
obvious place holders. I tried to avoid line wrap but it looks like
there is still some in there.

Look up TransferDatabase in Help. Particularly the TransferType
(acLink)

hope this helps,
RD


Option Compare Database
Option Explicit

Function fLinkTables() As Boolean
On Error GoTo Errhandler
Dim db As DAO.Database, rs As DAO.Recordset
Dim sSql As String, sOdbcDatabase As String
Dim sTableName As String

sSql = "SELECT [Catalog Update].[Tables] "
sSql = sSql & "FROM [Catalog Update]"

sOdbcDatabase = "ODBC; DSN=YourDataSource;UID=UserID;"
sOdbcDatabase = sOdbcDatabase & "PWD=Password;"
sOdbcDatabase = sOdbcDatabase & "DATABASE=DatabaseName;"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSql, dbOpenDynaset)

rs.MoveFirst
Do Until rs.EOF
sTableName = rs.Fields("Tables")
DoCmd.TransferDatabase acLink, "ODBC Database", sOdbcDatabase,
acTable, sTableName, sTableName
rs.MoveNext
Loop

fLinkTables = True

ExitPoint:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Function

Errhandler:
Debug.Print Err.Number & ": " & Err.Description
fLinkTables = False
Resume ExitPoint

End Function