Closing a db instance

[redFred]

My company just switched from Access 97 to Access 2003. In the following code of one db is a task that opens a new instance of access, opens another db, adds a recordset and then closes the new instance db. This worked fine in Access 97 but in Access 2003 the new instance does not close, everything else works ok. If someone with more VB savvy than I would please take a moment to look at my code for improvements, it would be much appreciative.

_____________________________


Dim acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String
Dim rst As DAO.Recordset

strDbName = "J:\EHENgase\Repair_Development\HDW-Stators-Rotating-BH-TOBI\Data Base\Sta_Rot_BH_Tobi_savings_Database.mdb"
Set acc = New Access.Application

Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
acc.OpenCurrentDatabase strDbName

Set rst = db.OpenRecordset("RDE TASKS", dbOpenDynaset)

' upload the data
With rst
.AddNew
![REA-IEN] = REA
![ENG MODEL (SPECIFIC)] = EngineModel
![ENG SECTION] = EngineSection
![ATA] = ATA
![PART NAME] = PartName
![PART NO] = PartNumber
![QUANTITY] = QTY
![Description] = RepairSubject
![PART PRICE] = PartPrice
![OLD REPAIR COST/OCC] = OldRepairCost
![NEW REPAIR COSTS/OCC] = NewRepairCost
![Estimated Number of parts per year] = EstPartsperYear
![Annual Savings] = AnnualSave
![Actionee] = Actionee
![Comments] = Comments
![Supplier] = IPT
![COPQ] = COPQ
![COMP/DROP DATE] = CompleteDate
![OUT PUT CATEGORY] = OutputCat

.Update
.Move 0, .LastModified
End With

rst.Close
' close the database instance

db.Close

'clean the variables

Set rst = Nothing
Set appAccess = Nothing
Set db = Nothing

MsgBox "Done"


End Sub

 

[john1958]

You could try

acc.Close
or
acc.Quit

before 'clean the variables


John

My company just switched from Access 97 to Access 2003. In the following code of one db is a task that opens a new instance of access, opens another db, adds a recordset and then closes the new instance db. This worked fine in Access 97 but in Access 2003 the new instance does not close, everything else works ok. If someone with more VB savvy than I would please take a moment to look at my code for improvements, it would be much appreciative.

_____________________________


Dim acc As Access.Application
Dim db As DAO.Database
Dim strDbName As String
Dim rst As DAO.Recordset

strDbName = "J:\EHENgase\Repair_Development\HDW-Stators-Rotating-BH-TOBI\Data Base\Sta_Rot_BH_Tobi_savings_Database.mdb"
Set acc = New Access.Application

Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
acc.OpenCurrentDatabase strDbName

Set rst = db.OpenRecordset("RDE TASKS", dbOpenDynaset)

' upload the data
With rst
.AddNew
![REA-IEN] = REA
![ENG MODEL (SPECIFIC)] = EngineModel
![ENG SECTION] = EngineSection
![ATA] = ATA
![PART NAME] = PartName
![PART NO] = PartNumber
![QUANTITY] = QTY
![Description] = RepairSubject
![PART PRICE] = PartPrice
![OLD REPAIR COST/OCC] = OldRepairCost
![NEW REPAIR COSTS/OCC] = NewRepairCost
![Estimated Number of parts per year] = EstPartsperYear
![Annual Savings] = AnnualSave
![Actionee] = Actionee
![Comments] = Comments
![Supplier] = IPT
![COPQ] = COPQ
![COMP/DROP DATE] = CompleteDate
![OUT PUT CATEGORY] = OutputCat

.Update
.Move 0, .LastModified
End With

rst.Close
' close the database instance

db.Close

'clean the variables

Set rst = Nothing
Set appAccess = Nothing
Set db = Nothing

MsgBox "Done"


End Sub

 

[redFred]

closing a db instance

Thanks John, acc.Quit worked,

acc.Close did not work for some reason.