Results 1 to 10 of 10
  1. #1
    claytoncramer is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    7

    Running multiple SQL commands from VBA


    I am new to Access. I am trying to import data from a spreadsheet into Access. I wrote a Java application that converts the data into a series of SQL commands. I looked for examples of how to run multiple SQL statements because I do not want to enter and run 2200 SQL statements by hand. I started with an example and coded this:
    Code:
    Public Function TestUpdate()
    On Error Resume Next
    
      Dim ws As Workspace
      Dim db As Database
      Dim strSQL As String
    
      Set ws = DBEngine.Workspaces(0)
      Set db = ws.Databases(0)
    
    On Error GoTo Proc_Err
      'start a transaction to ensure all updates are run or rolled back
      ws.BeginTrans
    
      '1st statement
      strSQL = "INSERT INTO incidents(ID, incident_date, state, city, dead, wounded, cause, category)"
    VALUES(1,0/0/1657,8,Farmington,2,1,5,1);"
      db.Execute strSQL, dbFailOnError
    
      '2nd statement
      strSQL = "INSERT INTO incident_weapon(incident_id, weapon_id) VALUES (1,1);"
      db.Execute strSQL, dbFailOnError
    
      '3rd statement
      strSQL = "INSERT INTO incidents(ID, incident_date, state, city, dead, wounded, cause, category);"
      db.Execute strSQL, dbFailOnError
      '4th statement
      strSQL = "INSERT INTO incident_weapon(incident_id, weapon_id) VALUES (2,4);'"
    
      'commit all changes
      ws.CommitTrans
    
    Proc_Exit:
      Set ws = Nothing
      Set db = Nothing
      Exit Function
    
    Proc_Err:
      ws.Rollback
      MsgBox "Error updating: " & Err.Description
      Resume Proc_Exit
    End Sub
    
    Private Sub Command0_Click()
    
    End Sub
    When I execute it, none of the commands are executed (at least not updating my tables), nor are there any error messages.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,155
    Parameters for text fields must be delimited with apostrophes.

    "INSERT INTO incidents(ID, incident_date, state, city, dead, wounded, cause, category) VALUES(1,'0/0/1657',8,'Farmington',2,1,5,1);"

    3rd SQL statement is incomplete.

    Is ID an autonumber field? I assume incidents and incident_weapon tables are related. How do you plan to get the appropriate ID to use as incident_id foreign key?

    If you want to provide sample files for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    claytoncramer is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    7
    id is indeed an autonumber field. incident_weapon.incident_id points to incidents.id. I use the incidents.id field to populate the incident_weapon.incident_id field. Does AUTONUMBER preclude setting the id number through INSERT? Thanks for string tip. It has been some years since I coded SQL.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,155
    INSERT action can populate autonumber field. Be sure to run Compact & Repair at the end of procedure.

    Your current code has the ID and incident_id fields hard-coded. Suppose you plan to make this dynamic?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,223
    why are you inserting a record at a time - 2200 times? would have thought this could be done with just 4 sql statements direct from Excel

    edit - on re reading the sql provided - just 2 sql statements

  6. #6
    claytoncramer is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    7
    Can you give me an example? There are six tables: incident; incident _weapon; incident_source, cause, category, state. Each incident row can have multiple weapon and source rows.

  7. #7
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,223
    OK - six tables means six queries

    Would need to see some example data and your table structure to provide a relevant example (you can zip both files and upload to here if you click on the go advanced button). but in principle your sql would look something like


    Code:
    INSERT INTO incidents(incident_date, state, city, dead, wounded, cause, category)
    SELECT DISTINCT incident_date, state, city, dead, wounded, cause, category FROM myLinkedExcelFile
    if you need to look up values such as a parent ID, providing you import that data first, you can look it up as part of your insert query

  8. #8
    claytoncramer is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    7
    Quote Originally Posted by Ajax View Post
    OK - six tables means six queries

    Would need to see some example data and your table structure to provide a relevant example (you can zip both files and upload to here if you click on the go advanced button). but in principle your sql would look something like


    Code:
    INSERT INTO incidents(incident_date, state, city, dead, wounded, cause, category)
    SELECT DISTINCT incident_date, state, city, dead, wounded, cause, category FROM myLinkedExcelFile
    if you need to look up values such as a parent ID, providing you import that data first, you can look it up as part of your insert query
    Here are the Excel file (only the incidents worksheet matters) and the Access database.
    Attached Files Attached Files

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,223
    OK - bit more complicated than I imagined, but still doable. Struggling to understand some of the tables in your db - the incident_weapon table has a number of duplicates for example and missing some values such as Pistol and Poison.

    If you were to colour code the excel headings to indicate which tables they go into that would help but for example to load the incidents table the sql would be close to what I provided before but would be modified to include a criteria

    Code:
    INSERT INTO incidents(incident_date, state, city, dead, wounded, cause, category)
    SELECT DISTINCT incident_date, state, city, dead, wounded, cause, category FROM myLinkedExcelFile
    WHERE [Year] is not null
    edit - just realised you have a separate table for state and your dates are split into separate columns so the query instead would be more like this

    Code:
    INSERT INTO incidents(incident_date, state, city, dead, wounded, cause, category)
    SELECT DISTINCT cdate([month] & "/" & [date] & "/" & [Year]), state.ID, city, dead, wounded, cause, category FROM myLinkedExcelFile INNER JOIN State ON myLinkedExcelFile.State=state.abbrev
    WHERE [Year] is not null

  10. #10
    claytoncramer is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2020
    Posts
    7
    I had been discouraged from attempting to import my spreadsheet into Access, but it is working very well. I managed to extract the data that I needed in Excel, and in Access with SQL queries it is, unsurprisingly, easier. Thanks all.

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

Similar Threads

  1. Running File System Commands in Module
    By shank in forum Modules
    Replies: 5
    Last Post: 12-18-2019, 12:46 PM
  2. Replies: 4
    Last Post: 09-13-2017, 02:29 PM
  3. Running multiple backends
    By Dansbo in forum Access
    Replies: 2
    Last Post: 12-15-2015, 06:26 AM
  4. Form_Current() running multiple times
    By caddcop in forum Forms
    Replies: 0
    Last Post: 04-01-2011, 09:00 AM
  5. Running subroutines and form commands in another database
    By cinciphantom in forum Programming
    Replies: 1
    Last Post: 03-16-2011, 01:55 PM

Tags for this Thread

Posting Permissions

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