Results 1 to 3 of 3

Run Query from Access Runtime in another database using Adodb Connection

  1. #1
    jaryszek is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    150

    Run Query from Access Runtime in another database using Adodb Connection

    Hi People,



    I am trying to create adodb connection from Access Runtime to another database in Access.

    I have split database and front-end is access runtime and back-end with normal access will be only in one computer.
    In back-end i have created a make table query and this is run by macro.

    I need VBA code to front-end users (access runtime) to join to back-end and run a query automatically.

    With this code i have an error:

    "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'"

    Code:
    Dim HurtowniaADO As ObjectDim ConnectionString As String
    Dim ZdanieSQL As String
    
    
    Set HurtowniaADO = CreateObject("ADODB.Connection")
    
    
    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & "C:\Users\ljar01\Desktop\Makro\Makro IBS\Nowy System\Pliki docelowe\" & "\TeleIBS_doc_wb.accdb"
    HurtowniaADO.Open ConnectionString
    ZdanieSQL = "OdswiezAll"
    HurtowniaADO.Execute ZdanieSQL
    Please help,
    Jacek Antek

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    5,363
    Instead of MAKE table query, use APPEND queries.
    empty the target table
    run the append query (instead of make)

  3. #3
    jaryszek is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    150
    Ok this is a good idea. Thank you ranman256.

    Could you please explain me and help a little how to do this.

    I think that i have to:
    1. Create a pernament table in back-end
    2. Create a link in front-end
    3. Create append query in front end from another database and add records to back-end table.

    It is a good direction?

    Best Wishes,
    Jacek

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access Runtime and SQL Connection
    By DCorby in forum SQL Server
    Replies: 1
    Last Post: 03-26-2013, 07:41 AM
  2. Replies: 1
    Last Post: 04-30-2012, 09:09 AM
  3. Replies: 4
    Last Post: 03-22-2012, 07:31 PM
  4. 2010 Runtime ADODB Connection Failure
    By SteveDurham in forum Access
    Replies: 2
    Last Post: 10-04-2011, 02:31 PM
  5. Runtime 2010 ADODB Connection failure
    By SteveDurham in forum Import/Export Data
    Replies: 5
    Last Post: 07-18-2011, 11:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums