Transfer all tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to deploy an update program to modify and add some tables to an
existing database in a split database application. My intention was to
create a database that would import all the tables including data from the
existing tables, add tables and modify existing tables, then export them to a
new table.

Since there are about 60 tables, I did not want to import each separately.
Using the docmd.transferdata command, can I nominate all tables to be
imported rather than write the line 60 times?
 
Sure. This query:

SELECT DISTINCT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "msys*") AND ((MSysObjects.Type)=1));

will return all of the non-system tables in the database. If you create a
recordset from it, you can loop through that and put the table name as a
variable in your transferdatabase command.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thanks Roger. It seems logical and I am working on it. Rather than invent
all the code, can you point me to a web site where it is all done for me? I
am struggling with rst and dbs and for, next loops trying to make it work.

Incidentally, I have not looked at it yet, but presumably I can use the same
approach to create a new database?

Thanks for your help
 
Hi Roger
Without doing a lot of debugging ( it is almost midnight here) I have the
following code
Set dbs = OpenDatabase(strOldTables)
strSQL = "SELECT DISTINCT MSysObjects.Name " & _
"FROM MSysObjects " & _
"WHERE (((MSysObjects.Name) Not Like ""msys*"") AND
((MSysObjects.Type)=1));"
Set rst = dbs.OpenRecordset(strSQL)

For Each msysobject In rst
DoCmd.TransferDatabase acImport, "Microsoft Access", _
strOldTables, acTable, rst.Name
Next

It stalls at the For statement. Any suggestions?
 
Sure. I usually include code, but in you're original question, it didn't
sound like you needed it. You've got a good start with creating the
recordset, but here's how I'd walk through it:

Do While Not rst.EOF
DoCmd.TransferDatabase acImport, "Microsoft Access", _
strOldTables, acTable, rst.Name
rst.MoveNext
Loop

Good Luck!
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Hi Roger
Latest problem is the following error. Record(s) cannot be read; no read
permission on <name>. (Error 3112)
Any suggestions?
 
It all finally works thanks to Roger. I hate reading newsgroups that end a
few steps from a successful outcome because the person was too lazy to post
the final code, so here it is.

Function funGetTables() ' Import the tables
from the nominated database
strOldTables = Forms!frmupgrade.txtLocation ' The file to import
from
Set dbs = OpenDatabase(strOldTables) ' Nominate the
database
strSQL = "SELECT DISTINCT MSysObjects.Name, MSysObjects.Type " & _
"FROM MSysObjects " & _
"WHERE (((MSysObjects.Name) Not Like ""msys*"") AND
((MSysObjects.Type)=1));"

Set rst = dbs.OpenRecordset(strSQL) ' Import the table
names

Do While Not rst.EOF ' Import each table
(rst!Name is the table name)
DoCmd.TransferDatabase acImport, "Microsoft Access", strOldTables,
acTable, rst!Name
rst.MoveNext
Loop

End Function
 
Back
Top