CODEWITHSUNDEEP
ms-accessvbscript
user441521
user441521

Reputation: 6998

Access VBScript ADODB.Command giving "Arguments are of wrong type" error

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

Upvotes: 1

Views: 920

Answers (1)

HansUp
HansUp

Reputation: 97101

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

Upvotes: 2

Related Questions