Need help with VB.Net code that creates an Access Database - how do Irelease the file?

T

The Mad Ape

Hello

I have an app that allows the user to create an access 'mdb' file
using vb.net (vs 2005). However I can not get this code to release the
database (it is locked). Please see my code below and my description
of the error message beside the offending line.

Thanks

TMA

Dim strFi As String = Application.StartupPath & "\Projects\" &
lblExport.Text & "\Export"
Dim fi As FileInfo
Dim SourceDir As String = strFi
Dim source As DirectoryInfo = New DirectoryInfo(SourceDir)

If Not source.Exists Then
source.Create()
End If

For Each fi In source.GetFiles("*.mdb")
If UCase(fi.Name) = UCase("reptree.mdb") Then

fi.Delete() ' code fails here on second time I run it
with the following error: The process cannot access the file
'reptree.mdb' because it is being used by another process. If you look
at my code below, I am closing and disposing the connection, yet the
locking file reptree.ldb remains. Why? How do I fix this?

Exit For
End If
Next

Dim catNewDB As New ADOX.Catalog
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & strFi & "\reptree.mdb")

'To make tables I use Adonet
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & strFi & "\reptree.mdb;")
Dim cmdRAW As New OleDb.OleDbCommand("CREATE TABLE
tblRepTreeRAW ( " & _
"AutoId int identity ," & _
"Stand NVarchar(100)," & _
"Point NVarchar(100)," & _
"Species NVarchar(3)," & _
"Height Numeric(20,2)," & _
"Age Numeric(20,2)," & _
"DBH Numeric(20,2)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)

Dim cmdSUM As New OleDb.OleDbCommand("CREATE TABLE
tblRepTreeSUM ( " & _
"AutoId int identity ," & _
"Stand NVarchar(100)," & _
"Species NVarchar(3)," & _
"AvgHeight Numeric(20,2)," & _
"AvgAge Numeric(20,2)," & _
"AvgDBH Numeric(20,2)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)

conn.Open()
Try
cmdRAW.ExecuteNonQuery()
cmdSUM.ExecuteNonQuery()

'add data to tblRAW
Dim strRAW As String
Dim icountRAW As Integer
Dim strSUM As String
Dim icountSum As Integer

Dim lviRaw As ListViewItem
Dim lviSum As ListViewItem

For Each lviRaw In lvRep.Items
strRAW = "insert into tblRepTreeRAW (Stand, Point,
Species, Height, Age, DBH) values ('" & lviRaw.SubItems(5).Text &
"','" & lviRaw.Text & "','" & lviRaw.SubItems(1).Text & "', " &
CDbl(lviRaw.SubItems(2).Text) & ", " & CDbl(lviRaw.SubItems(3).Text) &
", " & CDbl(lviRaw.SubItems(4).Text) & ")"
cmdRAW = New OleDbCommand(strRAW, conn)
icountRAW = cmdRAW.ExecuteNonQuery
Next

For Each lviSum In lvStRep.Items
strSUM = "insert into tblRepTreeSUM (Stand, Species,
AvgHeight, AvgAge, AvgDBH) values ('" & lviSum.Text & "','" &
lviSum.SubItems(1).Text & "', " & CDbl(lviSum.SubItems(2).Text) & ", "
& CDbl(lviSum.SubItems(3).Text) & ", " & CDbl(lviSum.SubItems(4).Text)
& ")"
cmdSUM = New OleDbCommand(strSUM, conn)
icountSum = cmdSUM.ExecuteNonQuery
Next

cmdRAW.Dispose()
cmdRAW = Nothing
cmdSUM.Dispose()
cmdSUM = Nothing
conn.Close()
conn.Dispose()
conn = Nothing

If Not lblBulk.Text = "BULK" Then
Beep()
MsgBox("Representative Tree GIS database created
sucessfully", MsgBoxStyle.Information)
End If

lblBulk.Text = "BULK"

Catch ex As OleDb.OleDbException
MessageBox.Show("Rep Tree Error. " & ex.Message,
"OleDbException")
cmdRAW.Dispose()
cmdRAW = Nothing
cmdSUM.Dispose()
cmdSUM = Nothing
conn.Close()
conn.Dispose()
conn = Nothing
lblBulk.Text = "BULK"
Exit Sub
Catch ex As Exception
MessageBox.Show("Rep Tree Error. " & ex.Message,
"GeneralException")
cmdRAW.Dispose()
cmdRAW = Nothing
cmdSUM.Dispose()
cmdSUM = Nothing
conn.Close()
conn.Dispose()
conn = Nothing
lblBulk.Text = "BULK"
Exit Sub
End Try
 
L

Larry Linson

This is one of the least good places I can think of to get help with VB.NET
code. Most Access developers didn't even use classic VB for database
applications, but many of us could easily read and understand the classic
VB.

The database you are using is likely a Jet database (but often mis-called an
"Access" database, even by Microsoft), the language VB.NET has many
constructs that look the same as Office VBA, but don't work quite the same,
and you'll be using ADO.NET as your access method (and ADO.NET is not just
evolutionary change to classic ADO -- it doesn't use the same object model,
nor necessarily the OLEDB that was the underlying technology for ADO). This
newsgroup is devoted to Q&A about using the Access database software, with
Jet, but also with other database engines.

You really need to ask in a VB.NET newsgroup... microsoft has a good many of
them, and you can probably find one devoted to database issues.

Larry Linson
Microsoft Office Access MVP


The Mad Ape said:
Hello

I have an app that allows the user to create an access 'mdb' file
using vb.net (vs 2005). However I can not get this code to release the
database (it is locked). Please see my code below and my description
of the error message beside the offending line.

Thanks

TMA

Dim strFi As String = Application.StartupPath & "\Projects\" &
lblExport.Text & "\Export"
Dim fi As FileInfo
Dim SourceDir As String = strFi
Dim source As DirectoryInfo = New DirectoryInfo(SourceDir)

If Not source.Exists Then
source.Create()
End If

For Each fi In source.GetFiles("*.mdb")
If UCase(fi.Name) = UCase("reptree.mdb") Then

fi.Delete() ' code fails here on second time I run it
with the following error: The process cannot access the file
'reptree.mdb' because it is being used by another process. If you look
at my code below, I am closing and disposing the connection, yet the
locking file reptree.ldb remains. Why? How do I fix this?

Exit For
End If
Next

Dim catNewDB As New ADOX.Catalog
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & strFi & "\reptree.mdb")

'To make tables I use Adonet
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & strFi & "\reptree.mdb;")
Dim cmdRAW As New OleDb.OleDbCommand("CREATE TABLE
tblRepTreeRAW ( " & _
"AutoId int identity ," & _
"Stand NVarchar(100)," & _
"Point NVarchar(100)," & _
"Species NVarchar(3)," & _
"Height Numeric(20,2)," & _
"Age Numeric(20,2)," & _
"DBH Numeric(20,2)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)

Dim cmdSUM As New OleDb.OleDbCommand("CREATE TABLE
tblRepTreeSUM ( " & _
"AutoId int identity ," & _
"Stand NVarchar(100)," & _
"Species NVarchar(3)," & _
"AvgHeight Numeric(20,2)," & _
"AvgAge Numeric(20,2)," & _
"AvgDBH Numeric(20,2)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)

conn.Open()
Try
cmdRAW.ExecuteNonQuery()
cmdSUM.ExecuteNonQuery()

'add data to tblRAW
Dim strRAW As String
Dim icountRAW As Integer
Dim strSUM As String
Dim icountSum As Integer

Dim lviRaw As ListViewItem
Dim lviSum As ListViewItem

For Each lviRaw In lvRep.Items
strRAW = "insert into tblRepTreeRAW (Stand, Point,
Species, Height, Age, DBH) values ('" & lviRaw.SubItems(5).Text &
"','" & lviRaw.Text & "','" & lviRaw.SubItems(1).Text & "', " &
CDbl(lviRaw.SubItems(2).Text) & ", " & CDbl(lviRaw.SubItems(3).Text) &
", " & CDbl(lviRaw.SubItems(4).Text) & ")"
cmdRAW = New OleDbCommand(strRAW, conn)
icountRAW = cmdRAW.ExecuteNonQuery
Next

For Each lviSum In lvStRep.Items
strSUM = "insert into tblRepTreeSUM (Stand, Species,
AvgHeight, AvgAge, AvgDBH) values ('" & lviSum.Text & "','" &
lviSum.SubItems(1).Text & "', " & CDbl(lviSum.SubItems(2).Text) & ", "
& CDbl(lviSum.SubItems(3).Text) & ", " & CDbl(lviSum.SubItems(4).Text)
& ")"
cmdSUM = New OleDbCommand(strSUM, conn)
icountSum = cmdSUM.ExecuteNonQuery
Next

cmdRAW.Dispose()
cmdRAW = Nothing
cmdSUM.Dispose()
cmdSUM = Nothing
conn.Close()
conn.Dispose()
conn = Nothing

If Not lblBulk.Text = "BULK" Then
Beep()
MsgBox("Representative Tree GIS database created
sucessfully", MsgBoxStyle.Information)
End If

lblBulk.Text = "BULK"

Catch ex As OleDb.OleDbException
MessageBox.Show("Rep Tree Error. " & ex.Message,
"OleDbException")
cmdRAW.Dispose()
cmdRAW = Nothing
cmdSUM.Dispose()
cmdSUM = Nothing
conn.Close()
conn.Dispose()
conn = Nothing
lblBulk.Text = "BULK"
Exit Sub
Catch ex As Exception
MessageBox.Show("Rep Tree Error. " & ex.Message,
"GeneralException")
cmdRAW.Dispose()
cmdRAW = Nothing
cmdSUM.Dispose()
cmdSUM = Nothing
conn.Close()
conn.Dispose()
conn = Nothing
lblBulk.Text = "BULK"
Exit Sub
End Try
Click to expand...
 
S

Stephany Young

At what point did you release catNewDB?


The Mad Ape said:
Hello

I have an app that allows the user to create an access 'mdb' file
using vb.net (vs 2005). However I can not get this code to release the
database (it is locked). Please see my code below and my description
of the error message beside the offending line.

Thanks

TMA

Dim strFi As String = Application.StartupPath & "\Projects\" &
lblExport.Text & "\Export"
Dim fi As FileInfo
Dim SourceDir As String = strFi
Dim source As DirectoryInfo = New DirectoryInfo(SourceDir)

If Not source.Exists Then
source.Create()
End If

For Each fi In source.GetFiles("*.mdb")
If UCase(fi.Name) = UCase("reptree.mdb") Then

fi.Delete() ' code fails here on second time I run it
with the following error: The process cannot access the file
'reptree.mdb' because it is being used by another process. If you look
at my code below, I am closing and disposing the connection, yet the
locking file reptree.ldb remains. Why? How do I fix this?

Exit For
End If
Next

Dim catNewDB As New ADOX.Catalog
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=" & strFi & "\reptree.mdb")

'To make tables I use Adonet
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=" & strFi & "\reptree.mdb;")
Dim cmdRAW As New OleDb.OleDbCommand("CREATE TABLE
tblRepTreeRAW ( " & _
"AutoId int identity ," & _
"Stand NVarchar(100)," & _
"Point NVarchar(100)," & _
"Species NVarchar(3)," & _
"Height Numeric(20,2)," & _
"Age Numeric(20,2)," & _
"DBH Numeric(20,2)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)

Dim cmdSUM As New OleDb.OleDbCommand("CREATE TABLE
tblRepTreeSUM ( " & _
"AutoId int identity ," & _
"Stand NVarchar(100)," & _
"Species NVarchar(3)," & _
"AvgHeight Numeric(20,2)," & _
"AvgAge Numeric(20,2)," & _
"AvgDBH Numeric(20,2)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)

conn.Open()
Try
cmdRAW.ExecuteNonQuery()
cmdSUM.ExecuteNonQuery()

'add data to tblRAW
Dim strRAW As String
Dim icountRAW As Integer
Dim strSUM As String
Dim icountSum As Integer

Dim lviRaw As ListViewItem
Dim lviSum As ListViewItem

For Each lviRaw In lvRep.Items
strRAW = "insert into tblRepTreeRAW (Stand, Point,
Species, Height, Age, DBH) values ('" & lviRaw.SubItems(5).Text &
"','" & lviRaw.Text & "','" & lviRaw.SubItems(1).Text & "', " &
CDbl(lviRaw.SubItems(2).Text) & ", " & CDbl(lviRaw.SubItems(3).Text) &
", " & CDbl(lviRaw.SubItems(4).Text) & ")"
cmdRAW = New OleDbCommand(strRAW, conn)
icountRAW = cmdRAW.ExecuteNonQuery
Next

For Each lviSum In lvStRep.Items
strSUM = "insert into tblRepTreeSUM (Stand, Species,
AvgHeight, AvgAge, AvgDBH) values ('" & lviSum.Text & "','" &
lviSum.SubItems(1).Text & "', " & CDbl(lviSum.SubItems(2).Text) & ", "
& CDbl(lviSum.SubItems(3).Text) & ", " & CDbl(lviSum.SubItems(4).Text)
& ")"
cmdSUM = New OleDbCommand(strSUM, conn)
icountSum = cmdSUM.ExecuteNonQuery
Next

cmdRAW.Dispose()
cmdRAW = Nothing
cmdSUM.Dispose()
cmdSUM = Nothing
conn.Close()
conn.Dispose()
conn = Nothing

If Not lblBulk.Text = "BULK" Then
Beep()
MsgBox("Representative Tree GIS database created
sucessfully", MsgBoxStyle.Information)
End If

lblBulk.Text = "BULK"

Catch ex As OleDb.OleDbException
MessageBox.Show("Rep Tree Error. " & ex.Message,
"OleDbException")
cmdRAW.Dispose()
cmdRAW = Nothing
cmdSUM.Dispose()
cmdSUM = Nothing
conn.Close()
conn.Dispose()
conn = Nothing
lblBulk.Text = "BULK"
Exit Sub
Catch ex As Exception
MessageBox.Show("Rep Tree Error. " & ex.Message,
"GeneralException")
cmdRAW.Dispose()
cmdRAW = Nothing
cmdSUM.Dispose()
cmdSUM = Nothing
conn.Close()
conn.Dispose()
conn = Nothing
lblBulk.Text = "BULK"
Exit Sub
End Try
Click to expand...
 
Top