Question about DAO Database and Recordset

[malaize2]

I have spent the last few hours searching forums and MSDN but still am not sure what some of the code in the following VB6 program is doing.

I cannot access the Help Files for VB6 because they apparently were not installed by the former IT Manager.
Could someone please look at the following section of code? I assume that the first 2 statements are creating new databases and the next 2 create recordsets.

What is a recordset?


********start of code******************
Dim dbUPSWorld As DAO.Database
Dim dbTableForUPSImport As Database
Dim rsUPSWorld As DAO.Recordset
Dim rsTableForUPSImport As DAO.Recordset

Private Sub Add()

Set rsTableForUPSImport = dbTableForUPSImport.OpenRecordset("SELECT * FROM TableForUPSImport")
**********end of code******************

What exactly is the last line of code doing?

Thanks a lot.

malaize

 

[gmmastros]

The code you show does not create a database. It creates a database object that you can reference in your code.

>> ********start of code******************
>> Dim dbUPSWorld As DAO.Database
>> Dim dbTableForUPSImport As Database

Declare database objects

>> Dim rsUPSWorld As DAO.Recordset
>> Dim rsTableForUPSImport As DAO.Recordset

Declare recordset objects

>> Private Sub Add()
>>
>> Set rsTableForUPSImport = dbTableForUPSImport.OpenRecordset("SELECT * FROM TableForUPSImport")

This line of code is gets all the data (Select *) from a table (From TableForUPSImport) from a database. The database is probably Microsoft access. Somewhere else, you must be opening the database. You should see lines of code like....

Set dbTableForUPSImport = New Database
Call dbTableForUPSImport.Open("PathToFile\DatabaseFile.mdb")

Those lines of code are 'connecting' the database object to the actual database iteslf.

Does this help? Have I cleared up your confusion? If not, let me know.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

DAO has an OpenDatabase or OpenConnection (if you're bypassing the JET engine, called ODBCDirect) method that needs to be used. This stuff is becoming archaic in the extreme, however, and you would be well advised to move to ADO if you can. For a little help:

http://msdn.microsoft.com/archive/d...nnectionStringParametersinMicrosoftAccess.asp

Note the word "archive" in the thread.

<What is a recordset?
A recordset is a cursor. Basically, it's a set of data with a movable pointer. You can wander around the set of data and look at it.

<What exactly is the last line of code doing?
It's creating a recordset object, using the dbtableforupsimport (what a mouthful! I generally use "db") database as the source of the data.

<It creates a database object that you can reference in your code.
It might clarify what George is saying to say that it declares your intention to create a database object. The object is actually created on the line of code that begins with "Set".

HTH

Bob

 

[malaize2]

Thanks for the help George and Bob.

Your explanations help me make more sense of it. The database that this program checks every 5 seconds is called upsworld.dbf. If the file has changed then the program somehow copies the upsworld.dbf data to an Access database that contains 2 tables. The tables are TableForUPSImport and UPSWorld. I have copied the program code below, but I don't understand where the upsworld.dbf file is actually being opened. I see where it is being checked for any changes (using the archive bit) but for the life of me don't understand what line(s) of code open it.

Dim dbUPSWorld As DAO.Database
Dim dbTableForUPSImport As Database
Dim rsUPSWorld As DAO.Recordset
Dim rsTableForUPSImport As DAO.Recordset


Private Sub Add()

Set rsTableForUPSImport = dbTableForUPSImport.OpenRecordset("SELECT * FROM TableForUPSImport")

With rsTableForUPSImport
'Set it to Add mode
.AddNew
'Enter the field values
.Fields("void").Value = rsUPSWorld!void
.Fields("serv_type").Value = rsUPSWorld!serv_type
.Fields("billing_op").Value = rsUPSWorld!billing_op
.Fields("return_op").Value = rsUPSWorld!return_op
.Fields("return_typ").Value = rsUPSWorld!return_typ
.Fields("satdelop").Value = rsUPSWorld!satdelop
.Fields("satpickop").Value = rsUPSWorld!satpickop
.Fields("shipnot1op").Value = rsUPSWorld!shipnot1op
.Fields("shn1_type").Value = rsUPSWorld!shn1_type
.Fields("shn1_comp").Value = rsUPSWorld!shn1_comp
.Fields("shn1_cont").Value = rsUPSWorld!shn1_cont
.Fields("shn1_phone").Value = rsUPSWorld!shn1_phone
.Fields("shn1_fax").Value = rsUPSWorld!shn1_fax
.Fields("shn1_intlf").Value = rsUPSWorld!shn1_intlf
.Fields("shn1_memo").Value = rsUPSWorld!shn1_memo
.Fields("shipnot2op").Value = rsUPSWorld!shipnot2op
.Fields("shn2_type").Value = rsUPSWorld!shn2_type
.Fields("shn2_comp").Value = rsUPSWorld!shn2_comp
.Fields("shn2_cont").Value = rsUPSWorld!shn2_cont
.Fields("shn2_phone").Value = rsUPSWorld!shn2_phone
.Fields("shn2_fax").Value = rsUPSWorld!shn2_fax
.Fields("shn2_intlf").Value = rsUPSWorld!shn2_intlf
.Fields("shn2_memo").Value = rsUPSWorld!shn2_memo
.Fields("descofgood").Value = rsUPSWorld!descofgood
.Fields("doconlyind").Value = rsUPSWorld!doconlyind
.Fields("spec_inst").Value = rsUPSWorld!spec_inst
.Fields("to_id").Value = rsUPSWorld!to_id
.Fields("to_comp").Value = rsUPSWorld!to_comp
.Fields("to_attn").Value = rsUPSWorld!to_attn
.Fields("to_addr").Value = rsUPSWorld!to_addr
.Fields("to_addr2").Value = rsUPSWorld!to_addr2
.Fields("to_addr3").Value = rsUPSWorld!to_addr3
.Fields("to_country").Value = rsUPSWorld!to_country
.Fields("to_zip").Value = rsUPSWorld!to_zip
.Fields("to_city").Value = rsUPSWorld!to_city
.Fields("to_state").Value = rsUPSWorld!to_state
.Fields("to_phone").Value = rsUPSWorld!to_phone
.Fields("to_fax").Value = rsUPSWorld!to_fax
.Fields("to_taxid").Value = rsUPSWorld!to_taxid
.Fields("rec_upsnum").Value = rsUPSWorld!rec_upsnum
.Fields("loc_id").Value = rsUPSWorld!loc_id
.Fields("resind").Value = rsUPSWorld!resind
.Fields("pkg_type").Value = rsUPSWorld!pkg_type
.Fields("weight").Value = rsUPSWorld!Weight
.Fields("trackno").Value = rsUPSWorld!trackno
.Fields("oversize").Value = rsUPSWorld!oversize
.Fields("pkg_ref1").Value = rsUPSWorld!pkg_ref1
.Fields("pkg_ref2").Value = rsUPSWorld!pkg_ref2
.Fields("pkg_ref3").Value = rsUPSWorld!pkg_ref3
.Fields("pkg_ref4").Value = rsUPSWorld!pkg_ref4
.Fields("pkg_ref5").Value = rsUPSWorld!pkg_ref5
.Fields("addlhand").Value = rsUPSWorld!addlhand
.Fields("codopt").Value = rsUPSWorld!codopt
.Fields("codamount").Value = rsUPSWorld!codamount
.Fields("cashonly").Value = rsUPSWorld!cashonly
.Fields("declvalop").Value = rsUPSWorld!declvalop
.Fields("declvalam").Value = rsUPSWorld!declvalam
.Fields("delconfop").Value = rsUPSWorld!delconfop
.Fields("delconfsig").Value = rsUPSWorld!delconfsig
.Fields("hazmatop").Value = rsUPSWorld!hazmatop
.Fields("sn1op").Value = rsUPSWorld!sn1op
.Fields("sn1type").Value = rsUPSWorld!sn1type
.Fields("sn1comp").Value = rsUPSWorld!sn1comp
.Fields("sn1cont").Value = rsUPSWorld!sn1cont
.Fields("sn1phone").Value = rsUPSWorld!sn1phone
.Fields("sn1fax").Value = rsUPSWorld!sn1fax
.Fields("sn1intlf").Value = rsUPSWorld!sn1intlf
.Fields("sn1memo").Value = rsUPSWorld!sn1memo
.Fields("sn2op").Value = rsUPSWorld!sn2op
.Fields("sn2type").Value = rsUPSWorld!sn2type
.Fields("sn2comp").Value = rsUPSWorld!sn2comp
.Fields("sn2cont").Value = rsUPSWorld!sn2cont
.Fields("sn2phone").Value = rsUPSWorld!sn2phone
.Fields("sn2fax").Value = rsUPSWorld!sn2fax
.Fields("sn2intlf").Value = rsUPSWorld!sn2intlf
.Fields("sn2memo").Value = rsUPSWorld!sn2memo
.Fields("verbconfop").Value = rsUPSWorld!verbconfop
.Fields("verbcont").Value = rsUPSWorld!verbcont
.Fields("verbphone").Value = rsUPSWorld!verbphone
.Fields("length").Value = rsUPSWorld!length
.Fields("width").Value = rsUPSWorld!Width
.Fields("height").Value = rsUPSWorld!Height
.Fields("merchdesc").Value = rsUPSWorld!merchdesc
.Fields("date").Value = Format$(Date, "mm/dd/yyyy")
'Update it
.Update
'Close it
.Close
End With
End Sub
Private Sub UpdateRecords()
Form1.Visible = True
Label1.Caption = "Updating records...."

Do While Not rsUPSWorld.EOF
Call Add
rsUPSWorld.MoveNext
Loop

'Close upsworld.dbf
rsUPSWorld.Close
Set rsUPSWorld = Nothing
dbUPSWorld.Close
Set dbUPSWorld = Nothing

'rsTableForUPSImport.Close
Set rsTableForUPSImport = Nothing
'dbTableForUPSImport.Close
Set dbTableForUPSImport = Nothing




Label1.Caption = "Updating records....Done!"
Call Finish
End Sub

Private Sub Finish()
Call SetArchive
Timer1.Enabled = True
Timer2.Enabled = True
End Sub



Private Sub Timer1_Timer()
On Error GoTo errorhandler
Dim archive As Integer

archive = GetAttr("upsworld.dbf") And vbArchive

If archive = 32 Then
Timer1.Enabled = False
Set dbUPSWorld = OpenDatabase("MOMImport.mdb")
Set rsUPSWorld = dbUPSWorld.OpenRecordset("SELECT * FROM UPSWorld")
Set dbTableForUPSImport = OpenDatabase("MOMImport.mdb")
'Delete records that are 21 days old
dbTableForUPSImport.Execute ("DeleteQuery")
Set rsTableForUPSImport = dbTableForUPSImport.OpenRecordset("SELECT * FROM TableForUPSImport")
Call UpdateRecords
End If

Exit Sub
errorhandler:
If Err.Number = 53 Then
MsgBox "file not found"
Else
MsgBox Err.Description, vbOKOnly, "Error"
End
End If
End Sub

Private Sub SetArchive()
On Error GoTo error_handler
Set fso = CreateObject("Scripting.FileSystemObject")
Set file = fso.GetFile("upsworld.dbf")
file.Attributes = 0
Exit Sub
error_handler:
MsgBox Err.Description, vbOKOnly, "Error"
End Sub

Private Sub Timer2_Timer()
Timer2.Enabled = False
Form1.Visible = False
Shell "temp.exe", vbNormalNoFocus
End
End Sub


thanks a lot, malaize

 

[BobRodes]

Well, I can see why. It would seem that our original architect changed horses in midstream a couple of times, and things got a bit confused. Here's the line:

Set dbTableForUPSImport = OpenDatabase("MOMImport.mdb")

It's in your Timer1_Timer event, and I can't see why. You don't want to keep opening your database every time you run the timer! Furthermore, both database objects are opening the same database! That's like opening two copies of Access, one to look at one table and the other to look at another table. I also notice that the closing of the database in the Add() procedure is commented out. Check this out:

'Close upsworld.dbf
rsUPSWorld.Close
Set rsUPSWorld = Nothing
[COLOR=red]dbUPSWorld.Close[/color]
Set dbUPSWorld = Nothing

'rsTableForUPSImport.Close
Set rsTableForUPSImport = Nothing
[COLOR=red]'dbTableForUPSImport.Close[/color]
Set dbTableForUPSImport = Nothing

See, the reason that it's commented out is when you close the database, it's closed. He probably wondered why that line (the second red line above) gave an error. Well, it's because he closed the same database on the first red line, so it was already closed.

All right. You're trying to transfer records from one table to another. This needs some rearchitecting:
1. Just call your Database object db. Open it once, leave it open until you're done using it.
2. Open your source recordset.
3. Open your target recordset.
4. Run through all the records you want to add from the source.
5. For each one of them, enter it into the target recordset. (addnew, update)
6. Close both of your recordsets.
7. Close your database.

See if you can clean up this code (frankly, it's a mess, at least the database manipulation is), and I'll help you along if you get stuck.

Good luck!

Bob

 

[malaize2]

Thanks a lot Bob!

I have contacted the original developer to see why he opened MOMImport.mdb twice.

One thing that I am having a hard time figuring out is where exactly in the code the upsworld.dbf database is being copied over to MOMImport.mdb. It has me stumped.

Thanks, malaize2

 

[BobRodes]

<One thing that I am having a hard time figuring out is where exactly in the code the upsworld.dbf database is being copied over to MOMImport.mdb.

That's because that isn't happening in this code. By the way, you don't open the database every time you call the timer. He didn't indent his if statement, and I missed it. Sorry about that; I always indent mine. However, it was still careless of me.

Now, what he's doing with his timer is checking the archive bit on the dbf file. If archive is true, he copies records from one table to another in an mdb file and sets archive to false. So, what's happening apparently is that another process is pulling records from a dbase file and putting them in the Access table. If the dbase file gets saved, the archive bit gets set, and this program picks that up. However, there is no place in this code that I can see that the dbase file gets accessed.

HTH

Bob