vbscript error trying to connect to access database

Question

This is the script:

Option Explicit
'Supported in Access 2003 and newer versions
'Add a reference to "Microsoft Access xx.0 Object Library"
'or change code to do Late Binding to support multiple Access versions
Private Sub Command1_Click()
    Dim oApp As Access.Application
    Set oApp = CreateObject("Access.Application")
    oApp.OpenCurrentDatabase "C:\student_id_Ft Lupton.mdb", Exclusive:=False
    oApp.ExportXML ObjectType:=acExportTable, _
               DataSource:="Ft_lupton", _
               DataTarget:="C:\fortlupton.xml"
    oApp.CloseCurrentDatabase
    oApp.Quit acQuitSaveNone
    Set oApp = Nothing
End Sub

I get the following error:

script: forltuptontest.vbs line: 6 Char: 14 Error: expected end of statement Code: 800A0401 Source: Microsoft VBScript compilation error

not sure what is wrong with line 6 character 14

after the wonderful help from the answer by:HansUp

here is a working version of the script which is exactly what I needed.

updated code looks like this:

Dim oApp
Set oApp = CreateObject("Access.Application")
Const acExportTable = 0
Const acQuitSaveNone = 2
oApp.OpenCurrentDatabase "C:\users\amoore19\desktop\Database\student_id_Ft Lupton.mdb", False
oApp.ExportXML 0, "Ft_lupton", "C:\users\amoore19\desktop\fortlupton.xml"
oApp.CloseCurrentDatabase
oApp.Quit 2
Set oApp = Nothing

Since you defined the constants, you could do oApp.ExportXML acExportTable if that seems clearer. — HansUp
– HansUp, Commented Feb 13, 2015 at 16:32

HansUp · Accepted Answer · 2015-02-13 16:49:50Z

2

Dim [variable name] As [something] is not supported in VBScript. All variables are variants, and Dim ... As triggers an error.

You can eliminate the error with this change ...

'Dim oApp As Access.Application
Dim oApp

Unfortunately, the script host complains about the first error it encounters and stops there. After you fix that first error, you will see more.

VBScript doesn't know about Access named constants, so you must supply the value of each constant instead of its name.

0 for acExportTable
2 for acQuitSaveNone

Or you could define the constants in your script if you prefer and then continue to use their names.

Const acExportTable = 0
Const acQuitSaveNone = 2

VBScript can't deal with named options for Access methods. So you must discard the option names and pass the option values in their expected order.

'oApp.OpenCurrentDatabase "C:\student_id_Ft Lupton.mdb", Exclusive:=False
oApp.OpenCurrentDatabase "C:\student_id_Ft Lupton.mdb", False
'oApp.ExportXML ObjectType:=acExportTable, _
'           DataSource:="Ft_lupton", _
'           DataTarget:="C:\fortlupton.xml"
oApp.ExportXML 0, "Ft_lupton", "C:\fortlupton.xml"

Finally, after you fix the errors, your script won't do anything because the executable statements are inside a subroutine: Command1_Click()

You could run the subroutine by adding Call Command1_Click() on the line after End Sub. However, I don't see why a subroutine is needed here; you can just discard Private Sub Command1_Click() and End Sub.

edited Feb 13, 2015 at 16:49

answered Feb 13, 2015 at 0:53

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

moore1emu Over a year ago

you are amazing. my updated coded has been added above

moore1emu Over a year ago

Hey Hans, one more question for you. if I have multiple .mdb files that i want to export to multiple or even one xml file. I know i can do this same export multiple times. but is there anyway to do like a *.mdb for the database and then export with a number stagger or export all to one xml. like adding one to the next?

HansUp Over a year ago

It would be easy to pull from each of several db files, but I don't see how to add their data to an existing XML file. Perhaps you could try a query which UNIONs the data from those tables and then export the query to XML.

moore1emu · Accepted Answer · 2015-02-13 17:30:47Z

1

the working solution from HansUp, which worked great is:

Dim oApp
Set oApp = CreateObject("Access.Application")
Const acExportTable = 0
Const acQuitSaveNone = 2
oApp.OpenCurrentDatabase "C:\users\amoore19\desktop\Database\student_id_Ft Lupton.mdb", False
oApp.ExportXML 0, "Ft_lupton", "C:\users\amoore19\desktop\fortlupton.xml"
oApp.CloseCurrentDatabase
oApp.Quit 2
Set oApp = Nothing`

edited Feb 13, 2015 at 17:30

answered Feb 13, 2015 at 17:08

moore1emu

49610 silver badges28 bronze badges

Collectives™ on Stack Overflow

vbscript error trying to connect to access database

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related