Hi,
I hope someone can provide some answers for me. I have a large application which creates an Access MDB and tables upon certain conditions. The problem is that the application does not release and close the Access MDB. I do not understand why.
Desktop: Windows 2000
Access Drivers : Microsoft Access Drive 4.00.6200.00
Application written in VB.net 2005 uses Microsoft ADOX 2.8 dll's
To eliminate all other possible causes, I created a shell application which only creates a brand new MDB on the c-drive and with a table to see if at the end of processing the application would release and close the access MDB. --- It did not. I provided the code below. If you need more information from me, please let me know.
Please HELP!
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim clsAccess As New Access
clsAccess.MDBCONNECT = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
clsAccess.ReconcileType = 31
clsAccess.CreateMDB()
clsAccess.OpenMDB()
clsAccess.CreateTable()
clsAccess.CloseMDB()
End Sub
End Class
Option Explicit On
Imports System.Data
Imports ADOx
Imports ADODB
Public Class Access
Private Const MG As String = "MG_XREF"
Private Const CM_PREP As String = "CALLMASTER_DIFF_PREP"
Private Const CM_APMS As String = "CALLMASTER_DIFF_APMS"
Private Const SM_PREP As String = "SCHEDULEMASTER_DIFF_PREP"
Private Const SM_APMS As String = "SCHEDULEMASTER_DIFF_APMS"
Private Const SS_PREP As String = "SUBSAMPLE_DIFF_PREP"
Private Const SS_APMS As String = "SUBSAMPLE_DIFF_APMS"
Private Const I61_PREP As String = "INTERFACE61_DIFF_PREP"
Private Const I61_APMS As String = "INTERFACE61_DIFF_APMS"
Private Shared m_iReconcileType As Integer
Private Shared m_sParameter As String
Private Shared m_sSectionName As String
Private Shared m_sDAO_Connect As String
Private Shared m_AccessConn As New ADODB.Connection
Private Shared m_sAccessDB As String = " "
Private Shared m_sPATHNAME As String = ""
Private Shared m_sMDBConnect As String = ""
Private Enum Reconcile
CallMaster = 1
CallMasterDiffPREP = 11
CallMasterDiffAPMS = 12
ScheduleMaster = 2
ScheduleMasterDiffPREP = 21
ScheduleMasterDiffAPMS = 22
SubSample = 3
SubSampleDiffPREP = 31
SubSampleDiffAPMS = 32
Interface61 = 4
Interface61PREP = 41
Interface61APMS = 42
MediaGuide = 5
End Enum
Public Sub CreateMDB()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~ Create the MDB
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim sDate As String = DateTime.Now.ToString("MM\-dd\-yy")
Try
'~~~ Generate MDB Name
Select Case m_iReconcileType
Case Reconcile.CallMasterDiffAPMS, Reconcile.CallMasterDiffPREP
m_sAccessDB = "c:\CM " & sDate & ".mdb"
Case Reconcile.ScheduleMasterDiffAPMS, Reconcile.ScheduleMasterDiffPREP
m_sAccessDB = "c:\SM " & sDate & ".mdb"
Case Reconcile.SubSampleDiffPREP, Reconcile.SubSampleDiffAPMS
m_sAccessDB = "c:\SS" & sDate & ".mdb"
Case Reconcile.Interface61APMS, Reconcile.Interface61PREP
m_sAccessDB = "c:\61 " & sDate & ".mdb"
Case Reconcile.MediaGuide
m_sAccessDB = "c:\MG " & sDate & ".mdb"
End Select
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
catNewDB.Create(m_sMDBConnect & m_sAccessDB)
catNewDB = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK)
Throw New Exception("CreateMDB() " & DateTime.Now.ToString, ex)
End Try
End Sub
Public Sub CreateTable()
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'~~~ Create the tables within the mdb
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim catDB As New ADOX.Catalog
Dim tblNew As New ADOX.Table
Try
catDB.ActiveConnection = m_AccessConn
'~~~ Create Media Guide Xref table
If m_iReconcileType = Reconcile.MediaGuide Then
With tblNew
.Name = MG
With .Columns
.Append("MGTtlid", ADOX.DataTypeEnum.adVarWChar)
.Append("TtlCde", ADOX.DataTypeEnum.adVarWChar)
.Append("MgTtl", ADOX.DataTypeEnum.adVarWChar)
.Append("Wip_Ttl_Na", ADOX.DataTypeEnum.adVarWChar)
.Append("MgArt", ADOX.DataTypeEnum.adVarWChar)
.Append("Wip_Art_Wtr_Pbr_Na", ADOX.DataTypeEnum.adVarWChar)
.Append("Upd_Uid", ADOX.DataTypeEnum.adVarWChar)
.Append("DateCreated", ADOX.DataTypeEnum.adVarWChar)
.Append("TotalWIP", ADOX.DataTypeEnum.adVarWChar)
End With
End With
catDB.Tables.Append(tblNew)
End If
'~~~ Create the Difference tables
Select Case m_iReconcileType
Case Reconcile.CallMasterDiffPREP
With tblNew
.Name = CM_PREP
With .Columns
.Append("Year", ADOX.DataTypeEnum.adVarWChar)
.Append("Call", ADOX.DataTypeEnum.adVarWChar)
.Append("State", ADOX.DataTypeEnum.adVarWChar)
.Append("Location", ADOX.DataTypeEnum.adVarWChar)
.Append("Category", ADOX.DataTypeEnum.adVarWChar)
.Append("AMFM_Wgt", ADOX.DataTypeEnum.adVarWChar)
.Append("Genre", ADOX.DataTypeEnum.adVarWChar)
.Append("Moneyclass", ADOX.DataTypeEnum.adVarWChar)
.Append("LType", ADOX.DataTypeEnum.adVarWChar)
.Append("College", ADOX.DataTypeEnum.adVarWChar)
.Append("BDS", ADOX.DataTypeEnum.adVarWChar)
.Append("AccountNumber", ADOX.DataTypeEnum.adVarWChar)
.Append("Suffix", ADOX.DataTypeEnum.adVarWChar)
End With
End With
catDB.Tables.Append(tblNew)
Case Reconcile.CallMasterDiffAPMS
With tblNew
.Name = CM_APMS
With .Columns
.Append("Year", ADOX.DataTypeEnum.adVarWChar)
.Append("Call", ADOX.DataTypeEnum.adVarWChar)
.Append("AccountNumber", ADOX.DataTypeEnum.adVarWChar)
.Append("State", ADOX.DataTypeEnum.adVarWChar)
.Append("Location", ADOX.DataTypeEnum.adVarWChar)
.Append("Category", ADOX.DataTypeEnum.adVarWChar)
.Append("Weight", ADOX.DataTypeEnum.adVarWChar)
.Append("Genre", ADOX.DataTypeEnum.adVarWChar)
.Append("District", ADOX.DataTypeEnum.adVarWChar)
.Append("LType", ADOX.DataTypeEnum.adVarWChar)
.Append("MoneyClass", ADOX.DataTypeEnum.adVarWChar)
.Append("College", ADOX.DataTypeEnum.adVarWChar)
.Append("Frequency", ADOX.DataTypeEnum.adVarWChar)
.Append("BDS", ADOX.DataTypeEnum.adVarWChar)
.Append("MusicUser", ADOX.DataTypeEnum.adVarWChar)
.Append("MG", ADOX.DataTypeEnum.adVarWChar)
End With
End With
catDB.Tables.Append(tblNew)
Case Reconcile.ScheduleMasterDiffPREP
With tblNew
.Name = SM_PREP
With .Columns
.Append("Year", ADOX.DataTypeEnum.adVarWChar)
.Append("Qtr", ADOX.DataTypeEnum.adVarWChar)
.Append("Call", ADOX.DataTypeEnum.adVarWChar)
.Append("Suffix", ADOX.DataTypeEnum.adVarWChar)
.Append("AccountNumber", ADOX.DataTypeEnum.adVarWChar)
.Append("Location", ADOX.DataTypeEnum.adVarWChar)
.Append("State", ADOX.DataTypeEnum.adVarWChar)
.Append("DOFLLoc", ADOX.DataTypeEnum.adVarWChar)
.Append("Date", ADOX.DataTypeEnum.adVarWChar)
.Append("From_Time", ADOX.DataTypeEnum.adVarWChar)
.Append("End_Time", ADOX.DataTypeEnum.adVarWChar)
.Append("TaperName", ADOX.DataTypeEnum.adVarWChar)
.Append("LogWeek", ADOX.DataTypeEnum.adVarWChar)
.Append("TestTape", ADOX.DataTypeEnum.adVarWChar)
.Append("Comments", ADOX.DataTypeEnum.adVarWChar)
.Append("Category", ADOX.DataTypeEnum.adVarWChar)
.Append("NPR", ADOX.DataTypeEnum.adVarWChar)
End With
End With
catDB.Tables.Append(tblNew)
Case Reconcile.ScheduleMasterDiffAPMS
With tblNew
.Name = SM_APMS
With .Columns
.Append("Year", ADOX.DataTypeEnum.adVarWChar)
.Append("Quarter", ADOX.DataTypeEnum.adVarWChar)
.Append("Call", ADOX.DataTypeEnum.adVarWChar)
.Append("AccountNumber", ADOX.DataTypeEnum.adVarWChar)
.Append("Location", ADOX.DataTypeEnum.adVarWChar)
.Append("State", ADOX.DataTypeEnum.adVarWChar)
.Append("Date", ADOX.DataTypeEnum.adVarWChar)
.Append("TimeOfDay", ADOX.DataTypeEnum.adVarWChar)
.Append("DOFLLoc", ADOX.DataTypeEnum.adVarWChar)
.Append("TaperName", ADOX.DataTypeEnum.adVarWChar)
.Append("LogTestTape", ADOX.DataTypeEnum.adVarWChar)
.Append("LogWeek", ADOX.DataTypeEnum.adVarWChar)
.Append("Comments", ADOX.DataTypeEnum.adVarWChar)
.Append("Category", ADOX.DataTypeEnum.adVarWChar)
.Append("NPR", ADOX.DataTypeEnum.adVarWChar)
End With
End With
catDB.Tables.Append(tblNew)
Case Reconcile.SubSampleDiffPREP
With tblNew
.Name = SS_PREP
With .Columns
.Append("Year", ADOX.DataTypeEnum.adVarWChar)
.Append("Quarter", ADOX.DataTypeEnum.adVarWChar)
.Append("Start_Date", ADOX.DataTypeEnum.adVarWChar)
.Append("End_Date", ADOX.DataTypeEnum.adVarWChar)
End With
End With
catDB.Tables.Append(tblNew)
Case Reconcile.SubSampleDiffAPMS
With tblNew
.Name = SS_APMS
With .Columns
.Append("Year", ADOX.DataTypeEnum.adVarWChar)
.Append("Quarter", ADOX.DataTypeEnum.adVarWChar)
.Append("Date", ADOX.DataTypeEnum.adVarWChar)
End With
End With
catDB.Tables.Append(tblNew)
Case Reconcile.Interface61PREP
With tblNew
.Name = I61_PREP
With .Columns
.Append("Pty_ID", ADOX.DataTypeEnum.adVarWChar)
.Append("CALL", ADOX.DataTypeEnum.adVarWChar)
.Append("SUFFIX", ADOX.DataTypeEnum.adVarWChar)
.Append("TOT_LOG_HRS_SCH", ADOX.DataTypeEnum.adVarWChar)
.Append("TOT_TAPE_HRS_ANLYZ", ADOX.DataTypeEnum.adVarWChar)
.Append("TOT_TAPE_HRS_SCH", ADOX.DataTypeEnum.adVarWChar)
.Append("TAPE_WGT", ADOX.DataTypeEnum.adVarWChar)
.Append("LOG_WGT", ADOX.DataTypeEnum.adVarWChar)
.Append("NLOG_WGT", ADOX.DataTypeEnum.adVarWChar)
End With
End With
catDB.Tables.Append(tblNew)
Case Reconcile.Interface61APMS
With tblNew
.Name = I61_APMS
With .Columns
.Append("STN", ADOX.DataTypeEnum.adVarWChar)
.Append("SUFFIX", ADOX.DataTypeEnum.adVarWChar)
.Append("TOT_LOG_HRS_SCH", ADOX.DataTypeEnum.adVarWChar)
.Append("TOT_TAPE_HRS_ANLYZ", ADOX.DataTypeEnum.adVarWChar)
.Append("TOT_TAPE_HRS_SCH", ADOX.DataTypeEnum.adVarWChar)
.Append("TAPE_WGT", ADOX.DataTypeEnum.adVarWChar)
.Append("LOG_WGT", ADOX.DataTypeEnum.adVarWChar)
.Append("NLOG_WGT", ADOX.DataTypeEnum.adVarWChar)
.Append("EFFECTIVE_DT", ADOX.DataTypeEnum.adVarWChar)
.Append("EXPORT_DT", ADOX.DataTypeEnum.adVarWChar)
End With
End With
catDB.Tables.Append(tblNew)
End Select
Catch ex As Exception
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK)
Throw New Exception("CreateTable() " & DateTime.Now.ToString, ex)
Finally
catDB.ActiveConnection = Nothing
catDB = Nothing
tblNew = Nothing
End Try
End Sub
Public Sub CloseMDB()
Try
m_AccessConn.Close()
m_AccessConn = Nothing
Catch ex As Exception
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK)
Throw New Exception("CloseMDB() " & DateTime.Now.ToString, ex)
End Try
End Sub
Public Sub OpenMDB()
Try
m_AccessConn = New ADODB.Connection
m_AccessConn.Open(m_sMDBConnect & m_sAccessDB)
Catch ex As Exception
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK)
Throw New Exception("OpenMDB() " & DateTime.Now.ToString, ex)
End Try
End Sub
#Region "CLASS PROPERTIES"
Public Property MDBCONNECT() As String
Get
MDBCONNECT = m_sMDBConnect
End Get
Set(ByVal value As String)
m_sMDBConnect = value
End Set
End Property
Public Property ReconcileType() As Integer
Get
ReconcileType = m_iReconcileType
End Get
Set(ByVal value As Integer)
m_iReconcileType = value
End Set
End Property
#End Region
End Class