470,622 Members | 1,737 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,622 developers. It's quick & easy.

Occasionally Locked Out of Access Database

Running VB coded from Excel to update information from an Access
When the code is run from Excel it works most times. But occasionally
I get error 4866 saying the Access database is opened exclusively by

Here is the VB code (it locks up on the line with asterisks

Private Sub CommandButton1_Click()
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Object
Dim xlWb As Object
Dim xlWs As Object
Dim recArray As Variant
Dim strDB As String
Dim fldCount As Integer
Dim recCount As Long
Dim iCol As Integer
Dim iRow As Integer
strDB = Worksheets("input").Range("C4")
Set xlApp = Application
Set ap = CreateObject("Access.Application")
ap.OpenCurrentDatabase (strDB)
cnt.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDB & ";"
rst.Open "Select * From [LOTS]", cnt ****************************
Set xlWb = ActiveWorkbook
Set xlWs = xlWb.Worksheets("FLOOR")
fldCount = rst.Fields.Count
For iCol = 1 To fldCount
xlWs.Cells(1, iCol).Value = rst.Fields(iCol - 1).Name
xlWs.Cells(2, 1).CopyFromRecordset rst
xlApp.Sheets("MIN BID BUYERS").Select
Set ap = Nothing
Set rst = Nothing
Set cnt = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
End Sub

Thanks in advance for your suggestions. Could there be something that
is left open in the closing statements of the subroutine above? Maybe
it works a few times and then is opened too many times - is there some
limit that is being exceeded possibly? Grasping for an explanation
Nov 12 '05 #1
0 3047

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by DebbieG | last post: by
1 post views Thread by Carlos Sol | last post: by
2 posts views Thread by Captain Nemo | last post: by
4 posts views Thread by hkappleorange | last post: by
15 posts views Thread by chaseexchange | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.