Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 10th, 2008, 02:54 AM
Authorized User
 
Join Date: Mar 2008
Posts: 74
Thanks: 2
Thanked 0 Times in 0 Posts
Send a message via ICQ to sektor
Default Can't open database thru VBA

I got simple code which copies data from Access to Excel.
It works, but NOT ALWAYS. After several successful retrieves, an error message appears:

Run-time error '-2147467259 (800040005)'
The database has been placed in a state by user 'Admin' on machine 'SEKTOR' that prevents it from being opened or locked.


How to avoid it? What wrong i did? All tables are closed.
Here's the code.


Sub GetData()

    ' Access.
    Dim adConn As New ADODB.Connection
    Dim adRs As New ADODB.Recordset
    Dim strConn As String
    Dim i As Integer
    Dim iFields As Integer

    ' Excel.
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet


    ' Set up connection string.
    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
              "Data Source=" & CurrentDb.Name

    ' Set mode and open connection.
    adConn.Mode = adModeRead
    adConn.Open strConn

    ' Write data to recordset.
    adRs.Open "tblMAIN", strConn, adOpenForwardOnly, adLockOptimistic

    ' Define number of fields.
    iFields = adRs.Fields.Count

    ' Create Excel objects.
    Set xlApp = New Excel.Application
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)

    With xlSheet

        ' Set names of headers.
        For i = 1 To iFields
            .Cells(1, i).Value = adRs.Fields(i - 1).Name
        Next

        ' Copy all records into worksheet.
        .Range("A2").CopyFromRecordset adRs

        ' Format retrieved table.
        .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes).Name = "AccessData"
        .Range("A1").CurrentRegion.Columns.AutoFit

    End With

    ' Show Excel.
    xlApp.Visible = True

    ' Clear references.
    Set adConn = Nothing
    Set adRs = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing

End Sub


 
Old September 15th, 2008, 11:44 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Is there an .ldb file in the folder that is being generated by your code? I notice you don't have any code to close the instance of Access on your computer, so that may cause the ldb file to be generated and not closed. Alternatively, are other users accessing this file at the same time as the code? Who is on the machine "SEKTOR?"





mmcdonal

Look it up at: http://wrox.books24x7.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
how to open an existing query in VBA michael193nj Access VBA 4 March 26th, 2008 05:09 PM
Access VBA - Open a different form... jonwitts Access VBA 3 April 27th, 2005 06:47 AM
Attempted to open a database that is already open person747 Access 10 September 3rd, 2004 04:31 PM
compacting an open mdb using VBA Dwight Pro VB 6 5 July 7th, 2004 01:53 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.