You're done, until you want to create a new table. Then
you'll have to learn how to loop through the tableDefs
and delete the tables you don't need. I think if you try to
call that twice you'll get an error on the second call, so
if it does error out, you'll want to set up a trap for the errata
in the code...
Private Sub CreateNewTable()
Dim strErr As String
Dim strSQL As String
Dim db As DAO.Database
On Error Goto LocalErr
...
etc.
...
Set db = Nothing
Exit Sub
LocalErr:
strErr = "ERROR " & CStr(Err.Number) & vbCrLf & _
Err.Description
MsgBox strError, vbCritical, "CreateNewTable"
End Sub
After you get that working, you can Loop through all
the tableDefs in the database and delete the tables that
are going to be rebuilt. After deleting the tables, I suggest
compacting the mdb as well, then rebuild the tables and
then compact again.
Something funny you'll notice, is that when you compact
an Access 2K mdb through code, it gets smaller than
when you've compacted it through Access.
--
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.
In the Access 2K file, create a link to the Access 97 table
you'd like to import from. Name the link:
linkEmployees
Create a Query in the Access 2K file. Name the
sQlinkEmployees
Save and close the query. Make sure it's named
sQlinkEmployees.
Double - click on it to make sure the records retrieved are
correct.
Right click on it and open it in design mode. We're going to
change it to a MakeTable query now and rename it.
Right click inside the table area and click on the QueryType,
change it from Select to Make Table. After you do this, you
should get a prompt for a new table name.
When it asks for the new table name, name the table:
tblEmployees
After it is given a name, save the query itself under a new name.
Name it:
0001-mtq-tblEmployees
The reason I put 0001 on the front is because if it's the first
query in a set of queries, you'll be able to know what it's doing,
how many queries there are, etc.
After it's saved under the new, name, double-click on it.
It should ask for confirmation and ask if you are sure you'd like
to create the new table.
Once you click yes, you'll have an extremily fast table creation
mechanism. You can call the db.Execute methods on the query
name and your done.
This creates a fresh new table within Access every time.
Hope this helps.
Dim db As DAO.Database
Dim strSQL As String
Set db = DAO.OpenDatabase("name.mdb")
strSQL = "0001-mtq-tblEmployees"
db.Execute strSQL
db.Close
Set db = Nothing
You're done.
--
Jim Carlock
http://www.microcosmotalk.com/
Post replies to the newsgroup.
Im working within an Access2000 database and writing code in VBA. What
i want to do is connect to an Access97 database through DAO and query
a table and import those records into my Access2000 database. The
tables have the same name and are similar in structure except they
have different names for fields. Ive been trying this mehtod but i
just cant seem to get it right:
Set db = ws.OpenDatabase(dbOld, True, True)
sSQL = "INSERT INTO tblGroup IN '" & CurrentDb.Name & "' " & _
"SELECT tblGroup.* FROM tblGroup.*;"
db.Execute sSQL
What am I doing wrong?
AGP
