Microsoft VBScript compilation error: Expected end

Question

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

Answer 1

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).

Answer 2

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.

Answer 3

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.