QueryDef syntax (1 Viewer)

[Rats]

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

 

[WayneRyan]

Peter,

If it doesn't already exist:

Set qdf = CurrentDb.CreateQueryDef("PALink")

Wayne

 

[Rats]

That could be it. No wonder I was bogged down. Thanks so much again. I'll give it a try.

 

[Pat Hartman]

You don't need to create a querydef. You can just execute the SQL string.