dcsimg
Results 1 to 15 of 15

Thread: [RESOLVED] Problem accessing Access 2007 database

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    10

    Resolved [RESOLVED] Problem accessing Access 2007 database

    Hello everyone,

    I'm using VBA 6.3 and I'm trying to import an Access 2007 database file (accdb). I've installed AccessDatabaseEngine.exe from Microsoft and checked Microsoft Office 12 access database engine object library from the References menu.

    Though, the following code:

    Code:
    Dim db As DAO.Database
    
    Set db = DBEngine.OpenDatabase("C:\mypath\mytable.accdb")
    always returns the error 3044 ("C:\mypath\mytable.accdb" is not a valid path). Of course, I've checked the path and the file name and they're ok. Do you know what's going on? Must I necessarily use ODBC?

    Thanks,
    Gunter

  2. #2
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Problem accessing Access 2007 database

    Why don't you use Mdb file instead of accdb ?

  3. #3
    Frenzied Member
    Join Date
    May 2006
    Location
    some place in the cloud
    Posts
    1,886

    Re: Problem accessing Access 2007 database

    Try attached example, it works here
    Attached Files Attached Files
    JG


    ... If your problem is fixed don't forget to mark your threads as resolved using the Thread Tools menu ...

  4. #4
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Problem accessing Access 2007 database

    Yep, i have also tried the following code is able to access accdb type database file .
    Code:
    Option Explicit
    Dim mdb As DAO.Database
    Dim rs As DAO.Recordset
    Private Sub Form_Load()
    
        Set mdb = OpenDatabase(App.Path & "\BD1.accdb")
        Set rs = mdb.OpenRecordset("Tabla1", dbOpenTable)
        
        Do Until rs.EOF = True
            List1.AddItem (rs!Campo1)
            rs.MoveNext
        Loop
        
    End Sub:D

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    10

    Re: Problem accessing Access 2007 database

    Hi,

    Thanks for your help. My issue is very strange indeed and makes me think of a bug in VBA.

    If I write this code:
    cnx.ConnectionString = Application.ThisWorkbook.Path & "\mydb.accdb"
    the instruction cnx.Open works but if I write this code:
    cnx.ConnectionString = "C:\mypath\mydb"
    the instruction cnx.Open returns an error!

    Though, in both cases the cnx.ConnectionString variable is strictly identical (I've debugged it step by step).

    Gunter

  6. #6
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Problem accessing Access 2007 database

    as i will also recommend don't hardcode the path of database .becuase long before i also got such type of issue .the following code is much better .
    Code:
    cnx.ConnectionString = Application.ThisWorkbook.Path & "\mydb.accdb"
    cnx.open

  7. #7

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    10

    Re: Problem accessing Access 2007 database

    Really? Did you get the same issue? It's beyond understanding... Currently, my database is located within the same directory of the Excel workbook but it could be in any other place. So it's not normal that the string written by hand could not be read properly. Maybe it's due to a bad interpretation of backslashes that are considered escape characters.

  8. #8
    Frenzied Member
    Join Date
    Jan 2009
    Location
    Watch Window(Shift+f9)
    Posts
    1,879

    Re: Problem accessing Access 2007 database

    Normally rarely i used VBA application . i have using the path in following way .and it is working like a charm in so many vb6 application .
    try to adapt the following way in your vbA Application .hope it might help .
    Code:
    con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & txtFilePath.Text)

  9. #9

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    10

    Re: Problem accessing Access 2007 database

    If I use the provider "Microsoft.Jet.OLEDB.4.0", I get an error when opening the database. I have to use "Microsoft.ACE.OLEDB.12.0" instead.

    Anyway, now that I'm connected, any basic request returns no row (RecordCount set to -1). Though, these requests of course return results if they are executed directly on the Access database.

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,324

    Re: Problem accessing Access 2007 database

    A RecordCount of -1 does not mean there are no records, in fact it means the opposite. There is an article which explains that in our Database Development FAQs/Tutorials (at the top of this forum)

    However, you should not be using RecordCount to check whether there are records or not, you should be using .BOF and .EOF instead, eg:
    Code:
    If rs.EOF And rs.BOF Then
      MsgBox "No records"
    Else
      MsgBox "There are record(s)"
    End If
    ...or if you want to work with the records, use the kind of loop shown in post #4.


    If I use the provider "Microsoft.Jet.OLEDB.4.0", I get an error when opening the database. I have to use "Microsoft.ACE.OLEDB.12.0" instead.
    Please don't ever just tell us "an error", because that is almost useless... give us some details (such as the error description, and which line of code it occurs on), as there is a very good chance that we can solve it easily.

  11. #11

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    10

    Re: Problem accessing Access 2007 database

    You're right, the request worked fine in fact! It's enough to loop until the Recordset equals EOF. So, I've had a look at my code and compared it with the source (given from a MVP). It turns out that I had forgotten to set the CursorLocation to adUseClient for the RecordCount to contain the right number of rows. Your article explains this technique...

    The error I was talking about is the same I mentioned in my first message, so I didn't find the need to recall it. Though, with ADODB, the error code becomes 80004005. Well, I can now connect to my DB and execute requests, but I really can't explain why en equivalent hard-coded connection string does not work. In the debugguer, both strings are stricty identical... It's as if VBA could not understand backslashes as I explained in a previous post.

  12. #12
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,324

    Re: Problem accessing Access 2007 database

    Setting the cursor location to client is usually not the way to deal with it, unless you have a particular need for a RecordCount (even if you do, it is debatable if that is the best solution).


    Regarding the error in the first post, there are two main likely causes that come to mind. The first is that DAO does not understand .accdb (which is certainly possible, as DAO is a long way out of date), and the other is that there is something about the actual path you are using (perhaps a security or virtualisation issue).

    Using ADO with Jet would fail for an .accdb file, because Jet doesn't understand them... I suspect that DAO is using Jet, so would expect the same problem.


    As to the hard-coded connection string issue, I suspect that the two strings are not totally identical (eg: one has an extra 'invisible' character, or something else that you overlooked), or that the rest of the code you are running is slightly different for each.

  13. #13

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    10

    Re: Problem accessing Access 2007 database

    Ok, anyway I don't need to know the record count in fact, so I removed this piece of code and just iterate over the RecordSet.

    Yeah, another MVP asked me that I switch to ADODB with the provider "Microsoft.ACE.OLEDB.12.0" to connect to the database.

    At last, I found the explanation!!! My directory had a grave accent on the i, which was almost imperceptible to the naked eye... So, you're right, that was just a stupid issue, but I haven't been able to see the difference in the debugguer (and the lenghts of the strings were of course the same).

    Well, I'm very sorry for trespassing on your time because of that stupid mistake. The point is closed now.

  14. #14
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,324

    Re: Problem accessing Access 2007 database

    Those little things can be so annoying because they are so hard to spot... but at least you've found it now


    As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
    (this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

    You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).

  15. #15

    Thread Starter
    New Member
    Join Date
    Mar 2011
    Posts
    10

    Re: Problem accessing Access 2007 database

    Done.

    Thank you again for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width