Help I can't make this code work

[Carriolan Shinobi]

Hi Ifound this code on the web for seting up a new database and then
exporting tables and text to it. However the VB editor highlights the
DIM statements in red. How can I fix this please

Sub exportdata()
    Dim ws As Workspace
    Dim db As Database
    Dim LFilename As String
    'Get default Workspace
    Set ws = DBEngine.Workspaces(0)
    'Path and file name for new mdb file
    LFilename = "c:\NewDB.mdb"
    'Make sure there isn't already a file with the name of the new
database
    If Dir(LFilename) <> "" Then Kill LFilename
    'Create a new mdb file
    Set db = ws.CreateDatabase(LFilename, dbLangGeneral)
    'For lookup tables, export both table definition and data to
new mdb file
    DoCmd.TransferDatabase acExport, "Microsoft Access",
LFilename, acTable, "Lookup Table1", "Lookup Table1", False
    'For data entry tables, export only table definition to new
mdb file
    DoCmd.TransferDatabase acExport, "Microsoft Access",
LFilename, acTable, "DataEntry Table1", "DataEntry Table1", True
    db.Close
    Set db = Nothing
End Sub

Thanks Carriolan

 

[Ken Snell [MVP]]

Set a reference to the DAO library in your ACCESS file.

 

[Carriolan Shinobi]

HI Ken
I already have Microsoft DAO 3.6 Object Library checked in the Visual
Basic Editor references. Hence I am at a loss. Rgds Carriolan

 

[Ken Snell [MVP]]

Hmmmm......

I wouldn't expect this to make a difference, but let's try using these two
Dim statements:

Dim ws As DAO.Workspace
Dim db As DAO.Database

 

[Ken Snell [MVP]]

Also, what object is running this code? A form or report in an .mdb file, or
in an .mde file? A data access page in an .mdb file? Using an .adp file?

 

[Carriolan Shinobi]

Ki Ken
At present it is being run from an On Click event from a form within
an mdb file, though ultimately it will be compiled into an mde. In
actual fact the On Click event runs a macro which runcodes the module.
Rgds Carriolan

 

[Ken Snell [MVP]]

Did adding DAO. to the Dim statements make any difference?

 

[Carriolan Shinobi]

Hi Ken as you suspected there was not much difference, but there was
enough! I cut and pasted your dim statements directly into the module,
which were black immediately. I then played around some more and found
that if I deleted the spaces in front of the original dim statements
and then re-indented them using the space bar they turned black. I now
can get the module to compile using both sets of dim statements. There
must have been some non-graphical characters which I picked up when
pasting from the web site. Now all I have to do is make it work -
thanks for your help. Rgds Carriolan.

 

[Carriolan Shinobi]

Ken
I have a couple of further questions. How would I modify the code:
1. So as to overwrite the newDB.mdb should it already exist; and
2. Setup the new database in the same folder as the existing database
wherever that may be.

Thanks Carriolan.

 

[Ken Snell [MVP]]

VBA has two built-in things that will help for question 1. Check out these
items in VBA Help:
Dir function for determining if a file already exsits.
Kill method for deleting a file.

The database's CurrentProject.Path method will return to you the path to the
folder where the database resides.

 

[Carriolan Shinobi]

HI Ken - thanks Carriolan

VBA has two built-in things that will help for question 1. Check out these
items in VBA Help:
Dir function for determining if a file already exsits.
Kill method for deleting a file.

The database's CurrentProject.Path method will return to you the path to the
folder where the database resides.