Database to be shut down (1 Viewer)

[dr223]

Hallo,

The application I have is dependent on a database which will be shut down this weekend, therefore I was instructed to check this specific module and determine how it is connecting to the database and how it fetchs the data. Then I have to take the table from the database and make it static within this access application and link it to it rather than making an ODBC connection. I would appreciate if someone shows me when it fetches the data - the connection is quite clear. When I grey out the connection no data is fetched... Therefore it definately dependent on the ODBC Connection.....

N/B: tblImportPatients is imported by the user when running the application. So initially, it deletes the old table and then creates a new table with the other fields populated.

Also, LoadRef - is a field within tblDuplicateData which is incremented when ever the tblimportedPatients is loaded. Therefore, at the bottom it reads the maximum value for LoadRef which is the recent imported table.

The red highligted section I dont understand at all.


Thanks

Please see the code below;

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", "maintain77", 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")
        
        [COLOR=red]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
[/COLOR]    
        rST1.Close
        rST2.Close
        cON.Close
        wRK.Close
        
    Set dBS = Nothing
    
    Beep
  
End Sub

 

[namliam]

The red section is the bit that updates your table... line by line:

While Not rST2.EOF
While we have not reached EOF (end of file/database/table/recordset)

rST1.AddNew
Add new record in rst1

For i = 0 To rST2.Fields.Count - 1
For every field found in rst2

rST1.Fields(i) = rST2.Fields(i)
fill the corresponding column in rst1 with data from the record and column in rst2

rST1.Fields(7) = LdRef
add the LoadRef, not sure what this is about? but basicaly the eighth column (0 = column #1) is filled

Next
go to the next column

rST1.Update
Update/save the record in rst1

rST2.MoveNext
Move to the next record in your rst2

Wend
Rince and repeat untill the "while" is met

Good Luck with your project!
(by the looks of it you will need it)

 

[dr223]

Thanks namliam, very helpful.

I have a question - when it tries to correspond column in rST1 with data from the record in column in rST2.

As you said ;

rST1.Fields(i) = rST2.Fields(i)
fill the corresponding column in rst1 with data from the record and column in rst2


rST1 comes from tblImportedData - which has one field called Pat_eid, this field is compared with a column from rST2 - rST2 is it a table from the ODBC Connection? if not then what is it comparing it with? I am not sure, I know tblImportedData is populated with more data from rST2... But where does it get the data from (rST2)

This is where I have problems...

Thanks for your help!

 

[namliam]

It is NOT comparing it is filling...
The FIRST column in rst1 gets the data from the FIRST column in rst2
The 2nd column in rst1 gets the data from the 2nd column in rst2
etc...

>>> It is NOT comparing it is filling... <<< Note my words:
fill the corresponding column in rst1 with data from the record and column in rst2

 

[dr223]

Ok, sorry my mistake. Then please could you let me know why it is connecting to the ODBC - odsaccess...

If to access data, where is it showing it or directing it really!!!

If I can ONLY know the data (such as a table name it accesses the data from) from odsaccess... I'll extract that data and make it static within the present access database and cancel the connection. Thats my idea really..

Any suggestions and help please!!!

 

[namliam]

It is NOT comparing it is filling...
The FIRST column in rst1 gets the data from the FIRST column in rst2
The 2nd column in rst1 gets the data from the 2nd column in rst2
etc...

>>> It is NOT comparing it is filling... <<< Note my words:
fill the corresponding column in rst1 with data from the record and column in rst2

What part of above do you not understand???

What part of my first post dont you understand???

OK Let me try again...

Your red code...

        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

"While Not rST2.EOF"
read all the records from rst2, rst2 beeing the table that is beeing accessed using the ODBC connection

"rST1.AddNew"
Add a new record to your rst1, your local table.

"For i = 0 To rST2.Fields.Count - 1"
For every column that exists in the ODBC connected rst2

"rST1.Fields(i) = rST2.Fields(i)"
Write the data from the ODBC connected rst2 to the local rst1, column by column...

"rST1.Update"
Save the record in the local rst1 table

 

[gemma-the-husky]

i have no experience with ADO, as opposed to DAO , BUT

strSQL = lblSQL.Caption & " (" & txtCodes & ")"
.........
.........
.........
Set wRK = CreateWorkspace("", "maintain", "maintain77", 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()

this is the bit thats doing it.

your rst2 is the tblimportpatients, which i think is emptied prior to running this process with this statement

dBS.Execute "DELETE tblImportPatients.* FROM tblImportPatients"

your rst1 is a ado record set, generated from the strsql textbox (i think)

do a msgbox(strsql) immediately before this line,to see exactly what it contains

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

now if either of these recordsets can't be determined due to a network error, its hard to know exactly what will happen - most likely you get a run time error and you get an empty dataset at the end of it. Perhaps your code handles the rte, and just jumps to an exit point somewhere.

Try using a breakpoint to trace the code. Clcik in the left margin to set/clear the breakpoint, then F8 to advance code a line at a time