none
Error from the VB script copy one Access mdb table to another accesss table. both are mdb files RRS feed

  • Question

  •             

    Error form the following vbs and get Expected end of statement.  

    MY pC installed Access 2016 open the mdb file.  Provider=Microsoft.Jet.OLEDB.4.0?

    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Test Files\db10.mdb")

    AccessConn.Open()
    Dim AccessCommand As New
    System.Data.OleDb.OleDbCommand("DELETE * FROM [Catlog]", AccessConn)
    AccessCommand.ExecuteNonQuery()
    AccessCommand.CommandText = "INSERT INTO [Catlog] SELECT * FROM [MS Access;DATABASE=C:\Test Files\db1 XP.mdb;].[Catlog]"
    AccessCommand.ExecuteNonQuery()
    AccessConn.Close()

    Thanks!

    Tuesday, May 30, 2017 1:33 PM

All replies

  • Hi Daphne1212,

    Since you install access 2016, you need modify the connection string with Microsoft.ACE.OLEDB.12.0; please modify your code like this:

    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test Files\db10.mdb";Persist Security Info=False;)
    
    AccessConn.Open()
    Dim AccessCommand  As New System.Data.OleDb.OleDbCommand("DELETE * FROM [Catlog]", AccessConn)
    AccessCommand.ExecuteNonQuery()
     AccessCommand.CommandText = "INSERT INTO [Catlog] SELECT * FROM [MS Access;DATABASE=C:\Test Files\db1 XP.mdb;].[Catlog]"
     AccessCommand.ExecuteNonQuery()
     AccessConn.Close()
    

    In addition, The ACE provider is not installed on Windows operating systems by default. You have to install it. The ACE provider is available in two forms: Microsoft Office Access database engine 2007 and  the Microsoft Access Database Engine 2010 Redistributable. The 2007 version is only available as a 32-bit component whereas the 2010 version offers both 32 and 64-bit options. You need to be careful which option you choose, because a wrong choice here is the most frequent cause for the error message.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 31, 2017 3:25 AM
  • Code looks familiar. ;-) The below code worked for me. I don't typically re-use Command object instances so you might want to re-create the instance for the INSERT:

            Dim AccessConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                                "Data Source=C:\Test Files\db10.mdb")
    
            AccessConnection.Open()
            Dim AccessCommand As New System.Data.OleDb.OleDbCommand("DELETE * FROM [Customers]", AccessConnection)
            AccessCommand.ExecuteNonQuery()
            AccessCommand = New System.Data.OleDb.OleDbCommand("INSERT INTO [Customers] SELECT * FROM [MS Access;DATABASE=C:\Test Files\db1 XP.mdb;].[Customers]", AccessConnection)
            AccessCommand.ExecuteNonQuery()
            AccessConnection.Close()

    If you are still encountering an exception (error) please post the exact error text so we can troubleshoot.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 31, 2017 2:31 PM
  •   Dim AccessConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Employee.mdb")

    or the second vbs:

    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Employees.mdb";Persist Security Info=False;)

    I installed Microsoft Office Access Database engine 2007 last year. Which 32 bit installed since my SSIS has Excel connection manager has to use the 32 bit version. Visual Studio 2012 comes with 32-bit version only and hence it cannot use ACE OLEDB 64-bit version, which comes with installation of office 2010 64-bit version.

    with size 51.3 mb

    Version: 12.0.4518.1031

    Error:C:\CopyTabel.vbs

    Line 1

    Char 23 

    (Char 16 for the second vbs)

    Error: Expected end of statement

    Code: 800A0401

    Source: Microsoft VBScript compilation error

    Thanks for all your helps!





    • Edited by Daphne1212 Thursday, June 1, 2017 7:53 PM
    Thursday, June 1, 2017 7:18 PM
  • Hi Daphne1212,

    ado.net is a part of .net framework, which does not support vb script, if you use vb script, please use ado.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 2, 2017 7:14 AM
  • Hi Cole,

    I'd like a scrip to update the Employee table in the A MDB from the Employee table in the B MDB.

    Access file is developed in the older version(MS 2000)  mdb file.  I open it in Access 2016. 

    I should use OLEDB.12.0 or OLEDB.4.0?  I did try both and failure.(Provider cannot be found. It may not be properly Code: 800A0E7A

    Dim connStr, objConn
    '''''''''''''''''''''''''''''''''''''
    'Define the driver and data source
    'Access 2007, 2010, 2013 ACCDB:
    'Provider=Microsoft.ACE.OLEDB.12.0
    'Access 2000, 2002-2003 MDB:
    'Provider=Microsoft.Jet.OLEDB.4.0
    ''''''''''''''''''''''''''''''''''''''
    connStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\Main_TravelCopy.mdb"
     
    'Define object type
    Set objConn = CreateObject("ADODB.Connection")
     
    'Open Connection
    objConn.open connStr    ------ Get error at here

    objConn.execute("DELETE * FROM SWORN")

     'Close connection and release objects
    objConn.Close
    Set rs = Nothing
    Set objConn = Nothing

    Thanks!





    • Edited by Daphne1212 Friday, June 2, 2017 1:30 PM
    Friday, June 2, 2017 12:15 PM
  • What is the error? Keep in mind that the user must have full permissions to the folder where the database is located in order to create the .ldb file when the database is opened.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 2, 2017 2:08 PM
  • .(Provider cannot be found. It may not be properly Code: 800A0E7A
    Friday, June 2, 2017 2:21 PM
  • .(Provider cannot be found. It may not be properly Code: 800A0E7A

    Sounds like a 32-bit/64-bit issue. Are you running the 32-bit or 64-bit scripting engine? It should be the same as the ACE OLEDB Provider that is installed on your machine.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, June 2, 2017 9:18 PM
  • Hi Daphne1212,

    This forum discusses and asks question about Data platform development using ADO.NET managed providers, If you use ADO with vbscript, I would suggest that you could post your issue vbscript forum for suitable support.

    Thanks for your understanding

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 5, 2017 7:18 AM