1

I have a VBscript that's opening up an Access DB, and I need to create a query from one of the tables, then export the table. I need to create the query because I need to format the number columns since in the DB they have like 10 decimal places but when exported only have 2. I need all 10. I don't control this Access DB, instead I download it so asking the source to make this query isn't an option.

The error with the below is: "ADODB.Command: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." what am I missing?

Dim oAccess
Set oAccess = CreateObject("Access.Application")

oAccess.OpenCurrentDatabase("dbfile here")

Dim oQry
Set oQry = CreateObject("ADODB.Command")

oQry.ActiveConnection = oAccess.CurrentProject.Connection
oQry.CommandType = 1
oQry.CommandText = "create view qryTable as select * from table"
oQry.Execute

Set oQry = Nothing
Set oAccess = Nothing
Improve this question

1 Answer 1

Reset to default
2

Giving .ActiveConnection a string value works for me.

oQry.ActiveConnection = oAccess.CurrentProject.Connection.ConnectionString

Beware if the Provider in that string is "Jet.OLEDB" and you're using 64 bit Windows, you must run the script in 32 bit mode; I used the version of cscript.exe from \Windows\SysWOW64.

However I'm unsure why you open an Access application instance and then create and execute an ADODB.Command. Perhaps that was an attempt to re-use the application's CurrentProject.Connection so that you wouldn't need to create one from scratch.

Whatever the reason was, if you have an Access instance anyway, you don't need the ADODB.Command. Just use the .Execute method from CurrentProject.Connection.

Dim oAccess, sSql

Set oAccess = CreateObject("Access.Application")
oAccess.OpenCurrentDatabase("dbfile here")

sSql = "create view qryTable as select * from [table]"
oAccess.CurrentProject.Connection.Execute sSql

Set oAccess = Nothing
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.