Using SQL and VBA in combination

Question

Okay I'm new here and will be my first question, I'm getting stuck with my code that I'm busy with. I keep bumping same error which is related to the Runtime error: 3129 in Access Visual Basic Now it is an importing button function set in a Public Sub check below my code:

Public Sub ImportDataTablebtn_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim tbl1 As DAO.TableDef
Dim tbl2 As DAO.TableDef
Dim fld1tbl1 As DAO.Field
Dim fld2tbl1 As DAO.Field
Dim fld3tbl1 As DAO.Field
Dim fld1tbl2 As DAO.Field
Dim fld2tbl2 As DAO.Field
Dim fld3tbl2 As DAO.Field

Set db = CurrentDb
Set tbl1 = db.TableDefs("Clicks Returns")
Set tbl2 = db.TableDefs("Oct 2015 Clicks Returns")
Set fld1tbl1 = tbl1.Fields("SKU")
Set fld2tbl1 = tbl1.Fields("Item Description")
Set fld3tbl1 = tbl1.Fields("Oct 2015 FIN YTD TY % Returns")
Set fld1tbl2 = tbl2.Fields("Sku")
Set fld2tbl2 = tbl2.Fields("Item Description")
Set fld3tbl2 = tbl2.Fields("F21")

DoCmd.RunSQL strSQL

strSQL = "INSERT INTO tbl1 (fld1tbl1, fld2tbl1, fld3tbl1)" & _
"SELECT fld1tbl2, fld2tbl2 fld3tbl2 FROM tbl2;"

Set db = Nothing

End Sub

Now I know some of my lines like the table rename and field might not really be required, though I thought that it would just make it easier with the code...

Please let me know where I'm making my error as I feel like going crazy by keep looking at it over and over.

In the database is the tables that I'm referring towards and I want to move the certain fields from the one table to the other table as it is imported excel sheets, I'm busy making it an automated system where someone would just click on buttons on a form and it will sort out the data.

Thank you for taking the time to read this essay and also answering it

You are trying to execute DoCmd.RunSQL strSQL before strSQL variable has been set to the INSERT statement. Move the strSQL = line above DoCmd.RunSQL. — user692942
– user692942, Commented Feb 25, 2016 at 14:49
Also in your strSQL, put a space between the close-paren and the word "SELECT". — Tab Alleman
– Tab Alleman, Commented Feb 25, 2016 at 14:55
@TabAlleman or a vbCrLf either will do the job. Prefer vbCrLf though because I can then replace them with <br /> when outputting for debug purposes and get nice neat SQL queries I can fire direct in the SSMS. — user692942
– user692942, Commented Feb 25, 2016 at 15:08

HansUp · Accepted Answer · 2016-02-25 15:28:36Z

4

There are a couple of mistakes in your code.

First the code will not substitute your variables for the table/field names. So the string SELECT fld1tbl2 is not converted to SELECT [Item Description].

Second you are trying to run your query before you have built it.

Finally Access uses VBA and not VBScript. It's a different language. While they have much in common there are some important differences. Stick to the the VBA tag and you will be more likely to get the answers you need.

I've refactored your code. This version builds and then runs the query.

Public Sub ImportDataTablebtn_Click()
Dim strSQL As String

    ' Build Query.
    strSQL = "INSERT INTO [Clicks Returns] " & _
        "(SKU, [Item Description], [Oct 2015 FIN YTD TY % Returns]) " & _
        "SELECT Sku, [Item Description], F21 FROM [Oct 2015 Clicks Returns];"

    ' Run Query.
    DoCmd.RunSQL strSQL
End Sub

edited Feb 25, 2016 at 15:28

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

answered Feb 25, 2016 at 15:07

David Rushton

5,0501 gold badge19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Heartless68 Over a year ago

Ah yes of course I didn't even think of going between VBA and VBscript lol thank you for pointing that out... Unfortunately I do not have the document at my home PC to actually test it out currently. I will only be Monday able to... Though just looking at what you are showing in your code it makes more sense and also I think and believe that it should work correctly. Thank you very much for the answer :D

Heartless68 Over a year ago

Thank you very much guys, really appreciate all the assistance :D

Collectives™ on Stack Overflow

Using SQL and VBA in combination

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related