468,257 Members | 1,431 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Occasionally Locked Out of Access Database

Running VB coded from Excel to update information from an Access
database.
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
admin.

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

Private Sub CommandButton1_Click()
ActiveWorkbook.RefreshAll
Worksheets("FLOOR").Range("A1:HH50000").Clear
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 & ";"
xlApp.Sheets("FLOOR").Select
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
Next
xlWs.Cells(2, 1).CopyFromRecordset rst
rst.Close
cnt.Close
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
here...
Nov 12 '05 #1
0 3007

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
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.