Code define

D

dr223

Registered User.
Local time
Today, 13:37
Joined
Nov 15, 2007
Messages
219
Hallo,

Please could anyone tell me what the following highligted in red codes means;

Option Compare Database
Option Explicit
Public qDF As QueryDef


Set qDF = cON.CreateQueryDef("", strSQL)

Set rST2 = qDF.OpenRecordset()

This code is inserted within an ODBC connection as shown below. I want to trace the reason of the connection and what is it linking too.. Am I looking on the right place?


Code: 
Sub FetchPatInfo()
    'this converts the pateids into a full patient list
    
    Dim i As Integer
    strSQL = lblSQL.Caption & " (" & txtCodes & ")"
    
    Set dBS = DBEngine(0)(0)
    
        dBS.Execute "DELETE tblImportPatients.* FROM tblImportPatients"
        Set wRK = CreateWorkspace("", "maintain", "maintain99", dbUseODBC)
        Set cON = wRK.OpenConnection("odsaccess", , , "ODBC;")
        cON.QueryTimeout = 0
     
        [COLOR=red]Set qDF = cON.CreateQueryDef("", strSQL)
[/COLOR]        
        Set rST1 = dBS.OpenRecordset("select * from tblImportPatients", dbOpenDynaset)
    
        [COLOR=red]Set rST2 = qDF.OpenRecordset()[/COLOR]
        
        Dim LdRef As Long
        
        LdRef = DMax("LoadRef", "tblDuplicateData")
        
        While Not rST2.EOF
            rST1.AddNew
            For i = 0 To rST2.Fields.Count - 1
                rST1.Fields(i) = rST2.Fields(i)
                rST1.Fields(7) = LdRef
            Next
            
            rST1.Update
            rST2.MoveNext
        Wend
    
        rST1.Close
        rST2.Close
        cON.Close
        wRK.Close
        
    Set dBS = Nothing
    
    Beep
  
End Sub

Thanks for any suggections..
 
The lines you've highlighted are readily quantifiable - I'll mention them first, then a bite more about context.

Set qDF = cON.CreateQueryDef("", strSQL)
Creates a temporary query definition object (temporary because no name parameter is supplied - it exists for use in your code procedure only).
Notice that it is being created upon the cON object though...

Set rST2 = qDF.OpenRecordset()
Opens a recordset upon that query definition which is really just based upon the statement defined with:
strSQL = lblSQL.Caption & " (" & txtCodes & ")"

With regard to context, that the querydef is created upon cON means it is a server query (again rememeber it's not created on the server, it's temporary - but it is connected to the server).
cON is a DAO connection object. Using ODBC Direct to establish the connection to the server. (ODBC Direct doesn't pass through the Jet engine as a connection to the server would otherwise using DAO). The settings for doing so

Set wRK = CreateWorkspace("", "xxxxx", "yyyy", dbUseODBC)
opening an ODBC Direct workspace, logging in with the supplied parameters.
Set cON = wRK.OpenConnection("odsaccess", , , "ODBC;")
opens a connection in that workspace. This Connection to an ODBC Direct server db is analogous to your Database object to the local database instance.
It uses the DSN "odsaccess" to retrieve the other settings to connect to your target RDBMS - this will be defined in your ODBC configuration manager.

The recordset you've opened upon that connection is essentially the same as DAO recordsets you'd open upon database objects otherwise. (Though you do have different recordset cursor type options in ODBC Direct).

It's worth mentioning that as of Access 2007, ODBC Direct has been removed from DAO. (Actually - removed from the version of DAO that now ships with 2007 the... You know I can't even be bothered to type it... Let's jsut say the "ACE DB Engine Library" :-s).
You can still access server databases through DAO then - but not via ODBC Direct but using a Database Object with a similar Connect parameter.

MS themselves recommend using ADO to offer similar functionality to ODBC Direct now. (Which is good to hear, given that they've seemed to slightly back off from promoting ADO in other respects).

Cheers.
 
Thank you very much for the explanation. When you typed - "With regard to context, that the querydef is created upon cON means it is a server query (again rememeber it's not created on the server, it's temporary - but it is connected to the server"

Is there any way I can update the code not to be connected to the server. This is mainly because the server will be shut down permanently and I have to update the coding here so that the application is not any longer dependent on the server.

If its a temporary query can't I update the code to run on the application rather than connecting to the server. Where is the query stored?

Thank you for your help!
 
Well, that depends.
Your server based query is defined using

strSQL = lblSQL.Caption & " (" & txtCodes & ")"

presumably the text in the caption of that label is a SQL statement something like
SELECT * FROM TableName WHERE

For you to be able to abandon the server source but maintain the same functionality, you'd need to have a similar table in your MDB source? (TableName)
With the same fields - and presumably same data!

With that in place you can abandon the new workspace and connection and just use a database object (i.e. your existing dBS) instead to open your recordset.

Bear in mind that with both tables local to your application, you would likely be better off transferring data using an Append query rather than recordset iteration.
(You could have done that with the server based table too really - but it would have involved a layer of Jet wrapping, which ODBC Direct was circumventing... not that it would have made all that much different in all likelihood).

Cheers.
 
On the form I found the lblSQL label and the caption is SELECT there is no table mentioned. No events declared....

Sorry, but this project was developed with someone who has left the organisation and I have been given limited time to upgrade it before the odsaccess is shut down. If it happens, the application won't work..

Any other ideas where the Select * from tablename might be..

Thanks for your help
 
A label is a really strange place to define a SQL statement anyway (IMNSHO).

Either there's more text on the label that you're not seeing (on subsequent lines?) or the body of the SQL is contained in txtCodes (though I don't see how - given the way it's built in code).

What is the content of txtCodes at the time of execution?
I was imagining something like "1,2,3" giving you a statemetn such as
SELECT * FROM TableName WHERE FieldName In (1,2,3)
there must be more somewhere...

Cheers.
 
This are the areas where txtCodes is used, probably it will be faster for you to notice what this chap was really doing..

Code: 
Private Sub cmdParse2_Click()
' feeds all the codes from the patEID table into a string variable
    TabChoice.Pages.Item(2).SetFocus
    Set dBS = DBEngine(0)(0)
    
    If togPatsEv = -1 Then
        Set rST1 = dBS.OpenRecordset("qryImportedPatIDs2", dbOpenDynaset)
    Else
        Set rST1 = dBS.OpenRecordset("qryImportedPatIDs", dbOpenDynaset)
    End If
    
    With rST1
    
        .MoveFirst
        txtCodes = .Fields(0)
         .MoveNext
        
        Do While Not .EOF
            txtCodes = txtCodes & "," & .Fields(0)
             .MoveNext
        Loop
        
         .Close
    
    End With
    
    cmdParse2.Enabled = False
    
    dBS.Execute "DELETE tblImportedPatIDs.* FROM tblImportedPatIDs"
    
    Set dBS = Nothing


Code: 
Sub Parse1()
    On Error GoTo erRhnD3
    If txtCodes = vbNullString Or IsNull(txtCodes) Then Exit Sub
    DoCmd.Hourglass True
    strSQL = lblSQL.Caption & " (" & txtCodes & ")"
    
    'Set wRK = CreateWorkspace("", "maintain", "maintain99", dbUseODBC)
    'Set cON = wRK.OpenConnection("odsaccess", , , "ODBC;")
    'cON.QueryTimeout = 0
 
    'Set qDF = cON.CreateQueryDef("", strSQL)
    
    'Set rST1 = qDF.OpenRecordset()
    Beep
    MsgBox "Parsing OK"
    cmdRun.Enabled = True
dOne3:
    'qDF.Close
    'cON.Close
    'wRK.Close
    DoCmd.Hourglass False
    Exit Sub
erRhnD3:
    Beep
        DoCmd.Hourglass False
    MsgBox "Parsing was not successful"
    cmdRun.Enabled = False
    GoTo dOne3
End Sub

Notice I greyed out the ODBC connection here but it still works.. Maybe might be a problem in future

Code: 
Private Sub txtCodes_Click()
    If txtCodes = "Paste EIDs here (in a sentence format eg - 1111111, 2222222, 3333333, etc)" Then txtCodes = ""
    
End Sub


Code: 
Private Sub TabChoice_Change()
    cmdRun.Enabled = False
    txtCodes = "Paste EIDs here (in a sentence format eg - 1111111, 2222222, 3333333, etc)"
End Sub


Thats all the sections "txtCodes" has been used..

lblSQL has no in the SELECT statement at the Caption (reconfirmed) tablename mentioned. I tried to such the whole project for a table in the ODS called informix.o_pat where I believe the data is extracted from, it doesnt exist. This makes me think that this has been declared at an event level probably not on the current modules.....
 
But that would make your code
SELECT (111,222,333)

Clearly pointless to be executed on the server - if it even supports that syntax (as opposed to SELECT 111,222,333 which would be supported).

You'll need to determine the source of strSQL at runtime.
Underneath
strSQL = lblSQL.Caption & " (" & txtCodes & ")"
insert
Debug.Print strSQL
Msgbox strSQL

You'll see the statement shown to you as the code runs - but it will also be output to the immediate window for later inspection.
See what it says there.

Cheers.
 
this is what it brings;

Select
o_prac.o_prac_eid,
o_prac.o_prac_go_id,
o_pat.o_pat_eid,
o_pat.o_pat_id,
o_pat.o_pat_birth_yr,
o_pat.o_pat_birth_mth,
o_pat.o_pat_curr_gender
FROM
o_prac,
o_pat
WHERE o_prac.o_prac_uid = o_pat.o_prac_uid
AND o_pat.o_pat_eid IN (11546, 37464, 2844646, 74747, ....)

Therefore, you are right it connects to the odsaccess and the tables are called o_pat and o_prac (these are the names of the tables, which is correct)

Another problem, I cant bring these tables and store them locally in access because they are build with over 100,000,000 records each access can't handle.
I have the tables in SQL, therefore I'll create an odbc connection to sql to connect to the 2 tables.

However, the present problem is where is the SELECT query written... definately not in all the modules as the Find utility brings no result.

Thank you very much for your help
 
You could search with a global find and replace utility (though many cost £$£$).
Or export all your forms to text and search those text files for the table names.
Look in your local tables (I am a fan of storing SQL statements in local tables).

Ultimately though - you'll find it in due course. And it should be simple - directly assigned at some stage during your code's execution - it simply must be.
Is finding the source your immediate priority?
Sorting out those tables seems to matter more I'd say.

If you're keeping the tables where they are, and your only task it that the DSN isn't going to be there much longer then your task has been pretty trivial all along.
Just establish a DSN that will be there with the same settings, or establish a DSNless connection / linked tables.

Cheers.
 
Ok , I think i'll create an DSN connection / linked table. Thank you very much for your help today. I'll keep you informed how it is getting on in due course.

Much appreciated
 
This is purely a sql server problem if you have any clue..

The tables in sql are saved as

dbo.o_pat and dbo.o_prac

Is there any way i can change the schema to be empty. So that it will be o_pat and o_prac...

Thanks
 
Every table must be "owned" (belong to a schema).
What's the problem you're having that would require you wanting to avoid that?
 
The problem is remember when we had a print out of the select statement it reads from tables o_prac and o_pat.

But in sql the tablea are saved as dbo.o_prac and dbo.o_pat? Meaning it will not find the tables or does it neglects the dbo?

Thanks
 
I see - that was your concern.
No - you'll be fine just providing the table name.
The dbo (database owner) is the default and the tables will be read anyway, without the prefix - assuming your connection credentials permit it.
(But that will manifest quickly once you try ;-)

Cheers.
 
Tried it! The connection is fine and the database is taking edges connecting to SQL, it takes more than 10 minutes and I had to terminate it. This is just for one record which should come up instantly. Mind you the table it is reading is built up 100,000,000 records. Could that be a reason ? Does it take that long?

Thanks
 
Which is the problem you're having, connecting to the database or requesting a row of data?

What code are you using now?
Well formed linked table requests should result in a pretty efficient query.
With 100M rows I can't honestly say that I'd be using linked tables myself though. ;-)
(I'd have switched over to ADO mode).

Cheers.
 
Ok have done!

I have linked to the two tables
dbo.o_prac
dbo.o_pat
from Sql server and then I renamed the linked tables to o_prac and o_pat...

Now I will have to disconnect the connection and make the subroutine read from the linked tables..

Please could you help me out in the code below to disconnect the ODBC connection and connect to the linked tables..It sounds abit confusing to me with the qdf still linking to the Con and you cant grey it out as it is still used in the loop below..

Thank you very much


Sub FetchPatInfo()
'this converts the pateids into a full patient list

Dim i As Integer
strSQL = lblSQL.Caption & " (" & txtCodes & ")"

Debug.Print strSQL
MsgBox strSQL

Set dBS = DBEngine(0)(0)

dBS.Execute "DELETE tblImportPatients.* FROM tblImportPatients"
Set wRK = CreateWorkspace("", "maintain", "maintain99", dbUseODBC)
Set cON = wRK.OpenConnection("odsaccess", , , "ODBC;")
cON.QueryTimeout = 0

Set qDF = cON.CreateQueryDef("", strSQL)

Set rST1 = dBS.OpenRecordset("select * from tblImportPatients", dbOpenDynaset)

Set rST2 = qDF.OpenRecordset()

Dim LdRef As Long

LdRef = DMax("LoadRef", "tblDuplicateData")

While Not rST2.EOF
rST1.AddNew
For i = 0 To rST2.Fields.Count - 1
rST1.Fields(i) = rST2.Fields(i)
rST1.Fields(7) = LdRef
Next

rST1.Update
rST2.MoveNext
Wend

rST1.Close
rST2.Close
cON.Close
wRK.Close

Set dBS = Nothing

Beep

End Sub
 
dr223 - just observed that this thread is virtually the same as another one you have running.
 
You must log in or register to reply here.

Similar threads

D
Database to be shut down
Replies
6
Views
1,665
gemma-the-husky
gemma-the-husky
D
Code meaning
Replies
1
Views
631
namliam
N
G
Can't get code to work
Replies
10
Views
1,462
Mile-O
Mile-O
M
Form is misunderstandig code
Replies
2
Views
657
MissCLuvr
M

Users who are viewing this thread

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