SQL syntax in VBA for an Append To query (1 Viewer)

R

Rats

Registered User.
Local time
Today, 10:18
Joined
Jan 11, 2005
Messages
151
The following is an SQL statement I have created in a query and copied into VBA. I have tried to follow the syntax shown in the Access Help but I keep getting syntax errors. Can anyone please tell me what I am doing wrong?
Code: 
 DoCmd.RunSQL "INSERT INTO Customers ( Advisor, ClientID, C1surname, C1firstname, C2Surname, C2firstname, stNo, StName, Locality, postcode ) IN 'C:\New Software Development\Master Achiever Program\PA Activity\PA Setting Up\PRDBinterface\proreport1.mdb'"
"SELECT [Client action].advisors, [Client action].ClientNo, [Client action].Client1Name1, [Client action].Client1Name2, [Client action].Client2Name1, [Client action].Client2Name2, [Client action].StreetNo, [Client action].StreetName, [Client action].Locality, [Client action].Postcode"
"FROM [Client action]"
"WHERE ((([Client action].ClientNo)=[forms]![client action amended]![clientNo]));"
 
Last edited:
W

WayneRyan

AWF VIP
Local time
Today, 03:18
Joined
Nov 19, 2002
Messages
7,122
Rats,

I think it might be as simple as --> Postcode" <-- There is no space, it's all runon --> "FR

You need to have a " " begore keywords like Select, From and Where


Code: 
[DoCmd.RunSQL "INSERT INTO Customers (Advisor, ClientID, C1surname, " & _
              "                       C1firstname, C2Surname, C2firstname, " & _
              "                       stNo, StName, Locality, postcode) " & _
              "IN 'C:\New Software Development\Master Achiever Program\PA Activity\PA Setting Up\PRDBinterface\proreport1.mdb' " & _
              "SELECT advisors, ClientNo, Client1Name1, Client1Name2, Client2Name1, Client2Name2, StreetNo, StreetName, Locality, Postcode " & _
              "FROM  [Client action] " & _
              "WHERE [Client action].ClientNo = " & [forms]![client action amended]![clientNo]

Wayne
 
P

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:18
Joined
Feb 19, 2002
Messages
43,262
There is nothing dynamic about the query. It is more efficient to leave it as a saved querydef.
 
R

Rats

Registered User.
Local time
Today, 10:18
Joined
Jan 11, 2005
Messages
151
Thanks Wayne and Pat for your help. Pat this question relates to a reply you gave me to a problem I was having with setting up a database to work on a variety of servers. You told me how to set up a paths table and to use a DLookup function to deal with the variety in server names. However, I had problems with the existing "Append to" queries and you said that "The only way to modify a structural element in a query is to create the SQL string with VBA".
My intention is to convert the path in the above query ie 'C:\New Software Development\Master Achiever Program\PA Activity\PA Setting Up\PRDBinterface\proreport1.mdb' to a DLookup(), but I need to be able to get the syntax right initially. I presume this will make the SQL "dynamic"??.

I hope this clarifies the situation. Once again thanks for the help.
 
W

WayneRyan

AWF VIP
Local time
Today, 03:18
Joined
Nov 19, 2002
Messages
7,122
Peter,

Something like this should do it:

Code: 
Dim qdf As QueryDef
Dim strSQL As String
Dim varDatabaseName As Variant
'
' Must be a variant because DLookUp might return a Null
'
varDatabaseName = DLookUp("[ServerPath]", "SomeTable", "[SomeField] = '" & Me.SomeCriteria & "'")

If IsNull(varServer) Then
   MsgBox("Unknown Database.")
Else
   strSQL = "INSERT INTO Customers (Advisor, ClientID, C1surname, " & _
            "                       C1firstname, C2Surname, C2firstname, " & _
            "                       stNo, StName, Locality, postcode) " & _
            "IN '" & varDatabaseName & "' " & _
            "SELECT advisors, ClientNo, Client1Name1, Client1Name2, Client2Name1, Client2Name2, StreetNo, StreetName, Locality, Postcode " & _
            "FROM  [Client action] " & _
            "WHERE [Client action].ClientNo = " & [forms]![client action amended]![clientNo] & ";"

   Set qdf = CurrentDb.TableDefs("SomeReusableQuery")
   qdf.SQL = sql
   qdf.Close
End If

hth,
Wayne
 
R

Rats

Registered User.
Local time
Today, 10:18
Joined
Jan 11, 2005
Messages
151
Hey Wayne thanks for the trouble you have gone to. Don't know what I would do without this forum it's a lifesaver. I'll give the code a whirl and see how it goes.
 
W

WayneRyan

AWF VIP
Local time
Today, 03:18
Joined
Nov 19, 2002
Messages
7,122
Peter,

Glad to help. I just typed the previous quickly.

Typo! Should be --> qdf.SQL = strSQL

see ya,
Wayne
 
R

Rats

Registered User.
Local time
Today, 10:18
Joined
Jan 11, 2005
Messages
151
Back again
I have used a portion of the code Wayne supplied to try and get the syntax correct. But there appears to be an error from the SELECT line down. I keep getting a compile error stating that "Expected: line number or label or statement or end of statement". I have tried various changes to the syntax of the lines in question but keep getting the same result. Any comments would be appreciated Thanks.

DoCmd.RunSQL "INSERT INTO Customers (Advisor, ClientID, C1surname, " & _
" C1firstname, C2Surname, C2firstname, " & _
" stNo, StName, Locality, postcode) " & _
"IN 'C:\New Software Development\Master Achiever Program\PA Activity\PA Setting Up\PRDBinterface\proreport1.mdb'"
"SELECT advisors, ClientNo, Client1Name1, Client1Name2, Client2Name1, Client2Name2, StreetNo, StreetName, Locality, Postcode " & _
"FROM [Client action]" & _
"WHERE [Client action].ClientNo = " & [forms]![client action amended]![clientNo] & ";"
 
W

WayneRyan

AWF VIP
Local time
Today, 03:18
Joined
Nov 19, 2002
Messages
7,122
Peter,

Notice how this line ends --> proreport1.mdb"

There is no space at the end.

When the "Select ..." in the next line is parsed, you have

proreport1.mdb'SELECT

It's all run together. The parser never has a chance. It's confused.

The same thing happens between the "From ..." line and the "Where ..." line.

Can you look at it with the Debugger? Can you set a breakpoint?

Wayne
 
R

Rats

Registered User.
Local time
Today, 10:18
Joined
Jan 11, 2005
Messages
151
I'm starting to feel like the Parser! OK Thanks again I'll use that info to try to fix it.
 
R

Rats

Registered User.
Local time
Today, 10:18
Joined
Jan 11, 2005
Messages
151
OK syntax now fixed. Just one last question (I hope!) What do you intend doing with the part of the code that says "set qdf = CurrentDb.TableDefs ("SomeReusableQuery") I'm not sure what I need to supply here.
Thanks
 
R

Rats

Registered User.
Local time
Today, 10:18
Joined
Jan 11, 2005
Messages
151
I cannot access any information on help re tabledefs and I cannot work out what Wayne intends with the line of code
Set qdf = CurrentDb.TableDefs("SomeReusableQuery")

What do I need to do to make this line of code function? :confused:
 
W

WayneRyan

AWF VIP
Local time
Today, 03:18
Joined
Nov 19, 2002
Messages
7,122
Peter,

Sorry for the delay.

It's QueryDef, not TableDef. The QueryDef is just a way that you
can generate the code and store it in a real Access query. It is
an alternative to the DoCmd.RunSQL.

If your current code works fine, you're OK. It is just another
technique.

Wayne
 
R

Rats

Registered User.
Local time
Today, 10:18
Joined
Jan 11, 2005
Messages
151
Hey Wayne glad to see you back on deck. I have attached a copy of the code that I have now set up. All works fine down to the line "Set qdf etc etc". I just don't understand what I am doing at this point. Am I creating a new query name in the text string? If so I am getting an error message saying "unknown member". Your help, as always , is appreciated.

Dim qdf As QueryDef
Dim strsql As String
Dim ServerPath As Variant
ServerPath = DLookup("[Link]", "links", "[linkname] = 'PAActivity'")
If IsNull(varserver) Then
MsgBox ("Unknown Path.")
Else
strsql = "INSERT INTO Customers (Advisor, ClientID, C1surname, " & _
" C1firstname, C2Surname, C2firstname, " & _
" stNo, StName, Locality, postcode) " & _
"IN '" & ServerPath & "' " & _
"SELECT advisors, ClientNo, Client1Name1, Client1Name2, Client2Name1, Client2Name2, StreetNo, StreetName, Locality, Postcode " & _
"FROM [Client action]" & _
"WHERE [Client action].ClientNo = " & [Forms]![client action amended]![ClientNo] & ";"

Set qdf = CurrentDb.QueryDef("PALink")
qdf.sql = strsql
qdf.Close
End If
 
W

WayneRyan

AWF VIP
Local time
Today, 03:18
Joined
Nov 19, 2002
Messages
7,122
Rats,

Well, better late than never ...

""FROM [Client action]" & _
"WHERE [Client action].ClientNo = " & [Forms]![client action amended]![ClientNo] & ";"
lient action].ClientNo = " & [Forms]![client action amended]![ClientNo] & ";"

This Generates --> "FROM [Client action]WHERE ...

You just need a space after [Client action]

Wayne
 
You must log in or register to reply here.

Similar threads

BrokenBiker
Lost in code and need some help
Replies
6
Views
1,599
SamDeMan
S
R
QueryDef syntax
Replies
3
Views
1,847
Pat Hartman
P
R
Append to not appending
Replies
0
Views
668
Rats
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom