Microsoft VBScript compilation error: Expected end of statement

2
votes

I am trying to insert some records into MS Access Table with the help of below VB Script. But when am trying to execute it, it's throwing Compilation error: Expected end of statement. Could someone please help me figure out where am I going wrong.

Private Sub Form_Click()
Dim dbs         As DAO.Database
Dim DbFullNAme  As String

DbFullName = "D:\G\Diamond\FINAL MS-Access\MS-Access project.accdb"
Set dbs = OpenDatabase(DbFullName)

dbs.Execute "INSERT INTO [2014_Status] ( Prompt, Project_Name, STATUS,Release_Name )SELECT     RoadMap.SPRF_CC, RoadMap.SPRF_Name, RoadMap.Project_Phase,RoadMap.Release_Name FROM RoadMap WHERE (((Exists (select 1 FROM [2014_Status] where RoadMap.SPRF_CC = [2014_Status].[Prompt]))=False));"
dbs.Close
End Sub
3
ms-accessvbscriptvba
The compilation error message is as , D:\G\Diamond\FINAL MS-Access\query1.vbs(2, 9) Microsoft VBScript compilation err or: Expected end of statement – user2412576
and just to verify, the above code is the code in query1.vbs? If not, please post the code for query1.vbs in your question. – Rich
I think you need () around the sql code - dbs.Execute ("...;") – Don George
Are you sure that you using this one correctly (((Exists (select 1 FROM [2014_Status] where RoadMap.SPRF_CC = [2014_Status].[Prompt]))=False)). I do not think that Access have or support this features. BUt in any case your where clause would look strange "...where null=false;" or "...where 1=false;" and in the any case it will fail because there is nothing to insert at all. Try to see how your SQL statement looks like before you execute it. – All Blond
@DonGeorge Please don't think, but check facts before posting: you must not use param list () when calling a sub in VBScript. – Ekkehard.Horner

3 Answers

7
votes

VBScript (as opposed to VBA or other dialects) does not support typed Dims. So 

Dim dbs         As DAO.Database
Dim DbFullNAme  As String

need to be

Dim dbs
Dim DbFullNAme

VBscript has no native OpenDatabase() function. You need to use ADO to connect to your Access 'database'. First create a connection

Set dbs = CreateObject("ADODB.Connection")

Then determine the connection string and

dbs.Open cs

The rest of your code should work.

Update wrt comment:

The error message:

D:\G\Diamond\FINAL MS-Access\query1.vbs(2, 9) Microsoft VBScript compilation error: Expected end of statement

prooves that the OT tried to write a VBScript (the addition of the misleading vba/access tags is (C) Pankaj Jaju).

2
votes

So lets break down the real reason why this code doesn't work.

You copied and pasted Visual Basic for Applications(VBA) into a .VBS(Visual Basic Script) file and expected it to work, I assume.

The problem with this is that VBA and VBScript are slightly different languages. Review the info section for both tags on stackoverflow when you get the opportunity.

For now lets just patch your code and maintain your DAO object so you don't have to reconstruct your Database usage with ADODB. 

ExecuteInsert
Sub ExecuteInsert()
    Dim dbs, DbFullName, acc
    Set acc = createobject("Access.Application")

    DbFullName = "D:\G\Diamond\FINAL MS-Access\MS-Access project.accdb"
    Set dbs = acc.DBEngine.OpenDatabase(DbFullName, False, False)
    dbs.Execute "INSERT INTO [2014_Status] ( Prompt, Project_Name, STATUS,Release_Name )SELECT     RoadMap.SPRF_CC, RoadMap.SPRF_Name, RoadMap.Project_Phase,RoadMap.Release_Name FROM RoadMap WHERE (((Exists (select 1 FROM [2014_Status] where RoadMap.SPRF_CC = [2014_Status].[Prompt]))=False));"
    dbs.Close
    msgbox "done"
End Sub

Changes made.

  1. Blocked your dim'd variables and removed As *** statements for vbscript compatibility
  2. Set an access object so you could maintain the remainder of your code.
  3. Added the acc.DBEngine. before OpenDatabase with additional parameters.
  4. Renamed your Sub from Form_Click to ExecuteInsert, then placed ExecuteInsert at the top of the code so that the vbscript activates the sub. If you just place a sub in a vbscript file, it will not necessarily run, you have to activate it directly.

This code is tested and functions. Best of luck to you.

0
votes

Adding to Ekkehard.Horner http://www.csidata.com/custserv/onlinehelp/vbsdocs/vbs6.htm

VBScript has only one data type called a Variant. A Variant is a special kind of data type that can contain different kinds of information, depending on how it's used. Because Variant is the only data type in VBScript, it's also the data type returned by all functions in VBScript.