Creating Blank Copy of BE from code

[Joe Williams]

I am trying to create a blank copy of a BE database via code. I have a
button which calls the open file dialog and allows the user to enter a
filename and location for the new database. Then I execute the CreateaDabase
code as follows:

Set dbNew = CreateDatabase(NewDBLoc, dbLangGeneral)
dbNew.Close

This creates a new database with the location and filename as specified in
NewDBLoc. No problem. Then I try to copy the STRUCTURE ONLY of the exisiting
tables to the new database with the following code:

DoCmd.TransferDatabase(acExport, , NewDBLoc, acTable, "Staff", , -1) where
Staff is the table name, dbNewLoc is the newly created database, and -1 is
the switch to copy structure only. Should work! But when it executes this
line of code I get the following error:

"The type isn't an installed database type or doesn't support the operation
you chose"

Can anyone help my find the error in my ways or provide another way to copy
the structure of the tables from the current BE to the newly created BE?
Thanks

Joe

 

[SteveS]

1) You didn't name the destination table. In your example, the empty commas
after "Staff" should have the name of the new table. You left it blank.

2) NewDBLoc is a string that is the FULL path to the new db: (example)
"C:\DBS\NWSales.mdb"

3) Don't use parenthesis


DoCmd.TransferDatabase acExport, "Microsoft Access ", NewDBLoc, acTable,
"Staff", "Staff", True

where NewDBLoc is a string that contains the *full path* to the new db location.

The above DoCmd.TransferDatabase line only transfers one object, in this case a
table named "Staff" to a table named "Staff". To transfer 5 more tables, you
would need 5 more DoCmd.TransferDatabase line in your code.

There will be one DoCmd.TransferDatabase for each object you want to transfer
(unless you use loops)


Steve

 

[Joe Williams]

THanks Steve,

I got it to work but there seems to be one additional problem. The code is
being called from the Front End, and when it creates the new back end and
using the export, structure only options as you listed, all it ends up doing
is recreating the linked table to the old Back End in the new database,
rather than create a new back end with no data as desired.

How can I create a NEW, CLEAN back end with no data, rather than just a link
to the old BE? Thanks

Joe

 

[SteveS]

Joe,

I just reread MS help for TransferDatabase and it states:

"You can use the TransferDatabase action to import or export data between the
*current* Microsoft Access database (.mdb) or Access project (.adp) and another
database. "

So TransferDatabase won't do what you want.

If you look at Help for CreateDatabase, it gives an example of creating a db and
adding fields/primary key/indexes. Since the tables are in the backend, you
could loop thru a table reading the field names and types, then create them in
the new db. Lots of code, but doable.

I ran a Google on "create database from code" and "create database" but didn't
find and Access code that looked like it might work.

I checked "The Access Web" and "Roger's Access Library" and didn't find any
examples of creating backend databases from code.

Would copying the backend to another folder, then running a delete query each of
the tables in the current backend work???


Steve

 

[Joe Williams]

Steve,

I think that creating a copy of the backend would work, but can I run the
delete query on the new BE without necessarily relinking the FE to this new
db?

 

[Joe Williams]

ok, so I am working through this problem. This is where I am at:

I create a new back end DB by using the following command:

DBEngine.CompactDatabase(OldDBLoc, NewDBLoc) where OldDBLoc is the original
file and
NewDBLoc is a new file name and path returned from the OpenFileDialog box.

Then I try to set to the new database and execute a SQL statement to clear
the tables using the following code:

Set dbsNew = DBEngine.Workspaces(0).OpenDatabase(NewDBLoc)
dbNew.Execute "DELETE * FROM STAFF"

When I execute the code, it creates the new back end database no problem but
fails when the second sets of commands is run. Specifically the error is
"OBJECT VARIABLE OR WITH BLOCK VARIABLE NOT SET"

I am thinking something is wrong with my SET statement. How do I properly
format the SET statement in order to perform the DELETE operation on the new
back end and not the currentDB ? I am trying to avoid re-linking to the new
back end becuase in this particular operation I just want to create a shell
of the back end and not relink the tables to it.

Thanks

Joe

 

[Gijs Beukenoot]

Joe Williams heeft uiteengezet op 27-11-2004 :

ok, so I am working through this problem. This is where I am at:

I create a new back end DB by using the following command:

DBEngine.CompactDatabase(OldDBLoc, NewDBLoc) where OldDBLoc is the original
file and
NewDBLoc is a new file name and path returned from the OpenFileDialog box.

Then I try to set to the new database and execute a SQL statement to clear
the tables using the following code:

Set dbsNew = DBEngine.Workspaces(0).OpenDatabase(NewDBLoc)
dbNew.Execute "DELETE * FROM STAFF"

When I execute the code, it creates the new back end database no problem but

<Cut>

If this is a copy and paste, you have a typo... One is dnsNew (notice
the s) and the other is dbNew (no s)

 

[Joe Williams]

Gijs,

THat was it! I can't believe I missed something that simple. Thanks much!