Creating Pass-through Queries on the fly

[Brad McCulloch]

Hi All,

I've got some code:

Dim strDiseases, FirstChunk, ConnectStr, strQueryName As String
Dim thisDB As Database
Dim fh1 As Integer
Dim QOb As Object

ConnectStr = "<<blanked out>>"

FirstChunk = "SELECT DISTINCT DATOWN.HI_DISEASE_GROUP.DESCRIPTION FROM
DATOWN.HI_DISEASE_GROUP"
FirstChunk = FirstChunk & " WHERE DATOWN.HI_DISEASE_GROUP.OWNER= '" &
Combo0.Text & "'"


fh1 = FreeFile
Open "D:\test.txt" For Output As fh1
Print #fh1, FirstChunk
Close fh1

Set thisDB = CurrentDb
strQueryName = "MakeDescs"
DoCmd.DeleteObject acQuery, strQueryName
Set QOb = thisDB.CreateQueryDef(strQueryName, FirstChunk)
QOb.Connect = ConnectStr

The output from the file printing bit in the middle shows that the query is
written as:

SELECT DISTINCT DATOWN.HI_DISEASE_GROUP.DESCRIPTION FROM
DATOWN.HI_DISEASE_GROUP WHERE DATOWN.HI_DISEASE_GROUP.OWNER= '****'

But when i look at the SQL view of the query is see...

SELECT DISTINCT DATOWN.HI_DISEASE_GROUP.DESCRIPTION
FROM [DATOWN].HI_DISEASE_GROUP
WHERE DATOWN.HI_DISEASE_GROUP.OWNER= '<<blanked out>>';

which includes some [] around the owner name [DATOWN] that causes it to
fail. If i manually edit the SQL view and remove the [] the query runs fine.
Of course, i want all this to be dynamic.

Any ideas why both access 97 and 2002 are doing this and how to stop it?

Thanks in advance

Regards,

B

 

[Guest]

I see that you are saving the sql before you are
making the connection. You need to work out
a way to do that the other way around. Perhaps,
if you can't connect a querydef with blank sql,
you might be able to work out some innocuous
sql you can use to create the connection before
putting the true sql into the querydef? Or what
happens if you start with a temp querydef instead
of a named querydef?

(david)