Read MS SQL 2000 table with "dynamic" pass through query

[Guest]

I'm not sure of all the correct terms for this question. I want to read
elements from an SQL table via ODBC. This is for authentication purposes and
I don't want to expose a pass through query where the user can see it. So, I
want to do the same thing within the code module and essentially create the
pass through query dynamically (or create it and then delete it). I am
familiar with creating the sql string, and then saying:

sqltext = "My SELECT statement"
Set myq = mydb.QueryDefs("qpassMyQuery")
myq.SQL = sqltext

Now, instead of adding the sqltext to an existing query, I want to create
the query on the fly. Can any one guide me or refer to a kb article? Thank
you.

 

[Dirk Goldgar]

I'm not sure of all the correct terms for this question. I want to
read elements from an SQL table via ODBC. This is for authentication
purposes and I don't want to expose a pass through query where the
user can see it. So, I want to do the same thing within the code
module and essentially create the pass through query dynamically (or
create it and then delete it). I am familiar with creating the sql
string, and then saying:

sqltext = "My SELECT statement"
Set myq = mydb.QueryDefs("qpassMyQuery")
myq.SQL = sqltext

Now, instead of adding the sqltext to an existing query, I want to
create the query on the fly. Can any one guide me or refer to a kb
article? Thank you.

Are you planning to open a recordset from this querydef to read the
data? If so, you should be able just to create a temporary querydef
without saving it. Something like ...

Dim mydb As DAO.Database
Dim myq As DAO.QueryDef
Dim myrs As DAO.Recordset

Set mydb = CurrentDb
Set myq = mydb.CreateQueryDef("")

With myq
.Connect = "<your connect string here>"
.SQL = "<your SQL string here>"
Set myrs = .OpenRecordset
End With

With myrs
' ... code to work with recordset ...
.Close
End With

Set myrs = Nothing
myq.Close
Set myq = Nothing
Set mydb = Nothing

 

[Guest]

Dear Dirk,

Yes I just want to open the dataset and read something. Here is my attempt
to fill in the spaces within your example. The line with the msgbox returns
an error "Variable Not Defined" and highlights the word SerialNumber in the
message. Here is how I filled in your routine. What did I miss?:

Dim mydb As DAO.Database
Dim myq As DAO.QueryDef
Dim myrs As DAO.Recordset
Dim sqltext As String

Set mydb = CurrentDb
Set myq = mydb.CreateQueryDef("")
sqltext = "SELECT SerialNumber, PracticeName FROM SyncData"

With myq
.Connect = "ODBC;DSN=PPM_700;Description=PracticePoint Manager
V7;Network=DBMSSOCN;Trusted_Connection=Yes"
.SQL = sqltext
Set myrs = .OpenRecordset
End With

With myrs
' ... code to work with recordset ...
.MoveFirst
MsgBox SerialNumber & " " & PracticeName, vbInformation, "This is a
test"
If SerialNumber = 123456 Then Authenticated = True
.Close
End With

Set myrs = Nothing
myq.Close
Set myq = Nothing
Set mydb = Nothing

 

[Dirk Goldgar]

Dear Dirk,

Yes I just want to open the dataset and read something. Here is my
attempt to fill in the spaces within your example. The line with the
msgbox returns an error "Variable Not Defined" and highlights the
word SerialNumber in the message. Here is how I filled in your
routine. What did I miss?:

Dim mydb As DAO.Database
Dim myq As DAO.QueryDef
Dim myrs As DAO.Recordset
Dim sqltext As String

Set mydb = CurrentDb
Set myq = mydb.CreateQueryDef("")
sqltext = "SELECT SerialNumber, PracticeName FROM SyncData"

With myq
.Connect = "ODBC;DSN=PPM_700;Description=PracticePoint Manager
V7;Network=DBMSSOCN;Trusted_Connection=Yes"
.SQL = sqltext
Set myrs = .OpenRecordset
End With

With myrs
' ... code to work with recordset ...
.MoveFirst
MsgBox SerialNumber & " " & PracticeName, vbInformation,
"This is a test"
If SerialNumber = 123456 Then Authenticated = True
.Close
End With

Set myrs = Nothing
myq.Close
Set myq = Nothing
Set mydb = Nothing

You're very close. You just need to correct your references to the
recordset's fields. Instead of this ...

MsgBox SerialNumber & " " & PracticeName, vbInformation,
"This is a test"
If SerialNumber = 123456 Then Authenticated = True

.... try this:

MsgBox !SerialNumber & " " & !PracticeName, _
vbInformation, "This is a test"
If !SerialNumber = 123456 Then Authenticated = True

Notice the bang (!) characters in front of SerialNumber and
PracticeName -- that tells VB that these are members of the default
collection -- the Fields collection -- of the "With" object, myrs. I'm
assuming that "Authenticated" is defined somewhere else, since I see no
declaration for it in the posted code.

Incidentally, you don't need this line:

.MoveFirst

.... since you know you just opened the recordset and hence will be at
the first record if there is one. You may need some logic to verify
that your query returned at least one record, though.

 

[Guest]

That worked! Thank you...I learned something today.