CODEWITHSUNDEEP
sqlvbams-access
Tim
Tim

Reputation: 1

MS Access SQL statement for append

I'm relatively new to MS Access and SQL.

I have a query in an access db to append one table to another, ordered ascending by a particular column, which I then use to add column for the purposes of assigning a unique identifier.

The query itself runs fine in access, however, I would like to write this into the VBA code of a form to action when a button is clicked.

The SQL view in access gives me the following code, but when I write it into my code it's stating that an operator is missing, can anybody make a suggestion?

Code:

DoCmd.RunSQL ("INSERT INTO tblImport ( CustomerAccount, OrderReference, OrderDate, DueDate, CustomerRef," & _
    "StockNo, OrderQuantity, AddressName, Add1, Add2, Add3, Town, County, PostCode, Country, ServiceLine )" & _
    "SELECT tblImport2.CustomerAccount, tblImport2.OrderReference, tblImport2.OrderDate, tblImport2.DueDate," & _
    "tblImport2.CustomerRef, tblImport2.StockNo, tblImport2.OrderQuantity, tblImport2.AddressName, tblImport2.Add1," & _
    "tblImport2.Add2, tblImport2.Add3, tblImport2.Town, tblImport2.County, tblImport2.PostCode, tblImport2.Country," & _
    "tblImport2.ServiceLine" & _
    "FROM tblImport2" & _
    "ORDER BY tblImport2.OrderReference")

Upvotes: 0

Views: 912

Answers (2)

Gustav
Gustav

Reputation: 56026

Save the query. Then you can call it from code:

CurrentDb.QueryDefs("NameOfYourAppendQuery").Execute

Or without the query:

Dim Sql As String

Sql = "<Your SQL string>"
Debug.Print Sql     ' Check SQL.
CurrentDb.Execute Sql

Upvotes: 1

Dave
Dave

Reputation: 4356

Are you aware that in VBA to specify a multiline string (as your query is here), you need to join the lines together like this? Otherwise VBA won't know it's supposed to be a single string.

mySql = "INSERT INTO tblImport ( CustomerAccount, OrderReference, OrderDate, DueDate, CustomerRef, StockNo, OrderQuantity, AddressName, Add1, Add2, Add3, Town, County, PostCode, Country, ServiceLine ) " & _
"SELECT tblImport2.CustomerAccount, tblImport2.OrderReference, tblImport2.OrderDate, tblImport2.DueDate, tblImport2.CustomerRef, tblImport2.StockNo, tblImport2.OrderQuantity, tblImport2.AddressName, tblImport2.Add1, tblImport2.Add2, tblImport2.Add3, tblImport2.Town, tblImport2.County, tblImport2.PostCode, tblImport2.Country, " & _
"tblImport2.ServiceLine " & _
"FROM tblImport2 " & _
"ORDER BY tblImport2.OrderReference; "

Upvotes: 1

Related Questions