[RESOLVED] Problem accessing Access 2007 database

[Gunter]

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:

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

[firoz.raj]

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

[jggtz]

Try attached example, it works here

[firoz.raj]

Yep, i have also tried the following code is able to access accdb type database file .

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

[Gunter]

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

[firoz.raj]

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 .

cnx.ConnectionString = Application.ThisWorkbook.Path & "\mydb.accdb"
cnx.open

[Gunter]

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.

[firoz.raj]

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 .

con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & txtFilePath.Text)

[Gunter]

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.

[si_the_geek]

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:

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.

[Gunter]

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.

[si_the_geek]

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.

[Gunter]

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.

[si_the_geek]

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).

[Gunter]

Done.

Thank you again for your help!