Import Tables

[Guest]

How would I modify this to import all my tables at once instead of creating
this string for each one? And can it be modified to import all with the
exception of a couple?

Function ImportTables()
DoCmd.TransferDatabase acImport, "Microsoft Access", "SourceDatabase",
acTable, "Table1", "Table2"
End Function

 

[Douglas J. Steele]

Try:

Function ImportTables()

Dim dbOther As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbOther = OpenDatabase("SourceDatabase")
For Each tdfCurr In dbOther.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"SourceDatabase", acTable, "Table1", "Table2"
End If
Next tdfCurr

dbOther.Close
Set dbOther = Nothing

End Function

To not import specific tables, you'd have to add another check, to look at
the name of the tdfCurr object, such as:

If (tdfCurr.Attributes And dbSystemObject) = 0 Then
Select Case tdfCurr.Name
Case "DontImport", "AnotherNoImport"
Case Else
DoCmd.TransferDatabase acImport, "Microsoft Access", _
"SourceDatabase", acTable, "Table1", "Table2"
End Select
End If

 

[John Vinson]

How would I modify this to import all my tables at once instead of creating
this string for each one? And can it be modified to import all with the
exception of a couple?

Function ImportTables()
DoCmd.TransferDatabase acImport, "Microsoft Access", "SourceDatabase",
acTable, "Table1", "Table2"
End Function

Just repeat the DoCmd.TransferDatabase line as many times as you wish,
using different table names.

Or, you could have a "driver" table with the names of all the tables
that you wish to import, open it as a Recordset and loop through it.

I must ask however - WHY!!?? Repeatedly importing tables would be a
very high overhead operation, would rapidly bloat your database...
what's the *purpose* of this exercise?

John W. Vinson[MVP]

 

[Guest]

If I am importing all tables what do i put in for table1 and table2. I want
all the tables to import with the same name they currently have. Also would
that 2nd IF statement go right after the first one, and would I need to
repeat it for each table I do not want to import?

Thanks,

 

[Douglas J. Steele]

Sorry: I did forget to change those, didn't I?

Assuming you want the table names to stay the same, use

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"SourceDatabase", acTable, tdfCurrName, tdfCurrName

 

[Guest]

I entered that into a module and changed the source database to the correct
path. And I received this error message.
Run-time Error '3011':
The Microsoft Jet database engine could not find the object ". Make sure the
object exists and that you spell its name and the path name correctly.

Here is exactly what I have, and the paths are correct.

Function ImportTables()

Dim dbOther As DAO.Database
Dim tdfCurr As DAO.TableDef

Set dbOther = OpenDatabase("J:\GCSDatabase\GCS.mde")
For Each tdfCurr In dbOther.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
DoCmd.TransferDatabase acImport, "Microsoft Access",
"J:\GCSDatabase\GCS.mde", acTable, tdfCurrName, tdfCurrName

End If
Next tdfCurr

dbOther.Close
Set dbOther = Nothing

End Function

Any idea why I am getting the error?

 

[Guest]

The owner of the database wants one button he can press to update the
database. Only he has access to that macro so there is no worries about
anyone else using it.

I have already tried splitting it to a FE BE database. The database becomes
way to slow when its used on the server. I tried turning both halves into an
MDE, compiled both databases as well as several other things and it would
still take 5mins to open a form.

So instead we have decided to create a button that deletes all the
relationships, deletes all the tables, then imports the new tables, so its a
one button update. It won't be something that is used very often as the
database has only been updated 3 times this year. Got most of it done, just
need to get this last peice working.

 

[Douglas J. Steele]

My typo.

That's supposed to be tdfCurr.Name, not tdfCurrName (there's a period in
front of Name)

Sorry about that.

However, this does point out that you haven't got Access set up to insist
that all variables be declared. While declaring all variables may seem to be
a nuisance, it can save you untold hours of debugging if you've mistyped a
variable name.

The second line of every module should be "Option Explicit". To have Access
insert this for you automatically, go into the VB Editor, select Tools |
Options, go to the Module tab and ensure that the "Require Variable
Declaration" option is checked. Sorry, but there's no way to get that added
to already-existing modules: you'll have to insert it yourself.

 

[Guest]

Thanks its all fixed now and works well.