VBS to run delete MS Access query

[Rob]

Hi all,

I am trying to write a VB Script run a query to delete all the rows in MS
Access table, or better yet build the query in the script and execute to
delete all the rows in table. The script and the database are on the same
machine.

Here is what I have written

Set objDb = CreateObject("Access.Application")
objDb.OpenCurrentDatabase strDbPathAndName

If Err.Number <> 0 Then
objLogFile.WriteLine Date & " " & Time & " ERROR - Connecting to the
database " & strDbPathAndName
objLogFile.WriteLine Date & " " & Time & " Error =" & Err.Number & " "
& Err.Description
blnNoError = False
Err.Clear
End if

objDb.Visible = False 'True if you desire to see MS Access load up

' Import UACCTBAL.TXT into ACCT_BAL using UBALACCT Import
ication -----------------------------------------------
strTableName = "ACCT_BAL"
strImportFile = "\SILO\DataFiles\UACCTBAL.TXT"
strImportSpec = "UBALACCT Import Specification"
strDelQuery = "qryACCOUNT_Delete_All"

' delete all the rows in the table first
objLogFile.WriteLine Date & " " & Time & " Running Delete Query " &
strDelQuery
'objDb.Execute("DELETE FROM " & strTableName)
objDb.DoCmd.OpenQuery strDelQuery

I get the following error:
Could not delete from specified tables

What am I doing wrong? Is there a better way of doing this, (I would prefer
to execute a query to delete all the rows in the table rather than running a
query)

Rob.

[S G]

Does this run client side?

"Rob" <dakk...@hotmail.com> wrote in message
news:uylFFBOWCHA.2644@tkmsftngp10...

[duncan]

Rob

The syntax of the SQL statement is incorrect. Change the line
"'objDb.Execute("DELETE FROM " & strTableName)" to :-

objDb.Execute("DELETE * FROM " & strTableName)

Note I have added the asterick immediately after the DELETE word.

I always execute a DELETE or INSERT statement as there is no recordset
to be returned.

I hope this helps

Duncan

"S G" <v-sa...@microsoft.com> wrote in message news:<#kamaDOWCHA.2540@tkmsftngp09>...

[Karel van der Walt]

I would much rather use ado to connect to access and execute a
(parameterized) update query through jet

something like (translate from jscript - i think you will manage)

var con = WScript.CreateObject("ADODB.Connection");

// first try DSN -less
con.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)};" +

"Dbq=c:\\projects\\actionit\\database\\ai.mdb;" +
"Uid=admin;" +
"Pwd=";

//objConnection.ConnectionString = "DSN=dsnAI;" +
// "Uid=admin;" +
// "Pwd=";

con.ConnectionTimeout = 0;
con.Open();

var rs = con.Execute('execute <yourupdatequery> '+parm);

rs = null;

con.Close();
con = null;