QueryDef syntax

Rats

Registered User.
Local time
Today, 19:32
Joined
Jan 11, 2005
Messages
151
After some very helpful assistance from this forum, I have set up the following code to create an "append To" query in a situation where the Db may be installed on a variety of servers. The Dlookup component finds the server path on a table called Links and that information is inserted into the SQL statement. The syntax appears to be correct down to the Set qdf line. At this point it appears to be incorrect as I keep getting "Method or Data Member not found" errors.

I have assumed that the text string in the following statement Set qdf = CurrentDb.QueryDef("PALink") is the new name of the query set up by this code and I have worked on that basis. If I am correct I am at a total loss as to the reason for the errors.

Any help would be appreciated
Thanks

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
 
Peter,

If it doesn't already exist:

Set qdf = CurrentDb.CreateQueryDef("PALink")

Wayne
 
That could be it. No wonder I was bogged down. Thanks so much again. I'll give it a try.
 
You don't need to create a querydef. You can just execute the SQL string.
 

Users who are viewing this thread

Back
Top Bottom