• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Unrecognized database format while importing from MS Access to MS Excel

ThrottleWorks

Excel Ninja
Hi,

I am trying to import data from MS Access to MS Excel.
I am using code from https://www.mrexcel.com/forum/excel-questions/498330-excel-vba-get-data-access-database.html to suite my requirement.

However I am facing bug as Unrecognized database format at 'Con1.Open' line.
Can anyone please help me understanding this issue.
Please note, I use Access 2010.

Code:
Sub GetData()

    Dim Con1 As ADODB.Connection
    Dim RecordSet As ADODB.RecordSet
 
    Const ConnString As String = _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\A\B\C\My Documents\Database4.accdb;Persist Security Info=False"
 
    Const SQL As String = "SELECT * FROM FromExceltoAccess"
 
    Set Con1 = New ADODB.Connection
    Con1.ConnectionString = ConnString
'Facing bug at below line 
Con1.Open
    Set RecordSet = New ADODB.RecordSet
    Call RecordSet.Open(SQL, Con1)
    Con1.Close
End Sub
 

ThrottleWorks

Excel Ninja
Hi @Chihiro sir, thanks for the help.
I changed it as advised. Now getting bug as Provider can not be found. It may not be properly installed.

Could you please help if you get time. Sorry if my questions are silly.
Have a nice day ahead.
 

ThrottleWorks

Excel Ninja
By the way, it seems below mentioned code is working for me for importing data from MS Access to MS Excel. Thanks.

However, the data is populated without headers. Is it normal behaviour or do I need to amend my code to get column headers.

Code:
'Working

Sub ImportFromAccess()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.RecordSet
    Dim sQRY As String
    Dim strFilePath As String
   
    'Replace the ‘DatabaseFolder’ and ‘myDB.accdb’ with your DB path and DB name
    strFilePath = "\\A\B\C\My Documents\Database4.accdb"
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.RecordSet
   
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strFilePath & ";"
   
    'Replace ‘tblData’ with your Access DB Table name or Query name from which you want to download the data
    sQRY = "SELECT * FROM FromExceltoAccess"
   
    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
   
    Sheet1.Range("A1").CopyFromRecordset rs
    rs.Close
   
    Set rs = Nothing
   
    cnn.Close
    Set cnn = Nothing
    MsgBox "Done !"
End Sub
 

Chihiro

Excel Ninja
Yes it is, normal behavior. To import header. You need to loop through Fields of the Recordset.

Ex.
Code:
Dim iCols As Long
rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly

For iCols = 0 To rs.Fields.Count - 1
    Sheet1.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next

Sheet1.Range("A2").CopyFromRecordset rs
 

ThrottleWorks

Excel Ninja
Hi @Chihiro sir, this is not urgent to me. You may reply if you get time.

I have 3 doubts.
I created a dummy Access database to import in excel, this is for learning purpose.

1.
Would you recommend the above mentioned code for this task.
Or if you had time to advise, would you recommend different code.
I mean, does my code leaves any loopholes.

2.
need Microsoft.ACE.OLEDB.12.0 as provider for *.accdb. Not Jet.
Does this mean, I need to check file extension of database before writing this line.

3.
I guess I know what is 'adLockReadOnly' however do not have idea, why 'adOpenStatic' is used.

rs.Open sQRY, cnn, adOpenStatic, adLockReadOnly
As mentioned above, please reply only if you get time. Thanks.

 

Chihiro

Excel Ninja
Heh, I consider myself jack-of-all trades, master of none ;)

For majority of my career I've worked with or for small to mid size entrepreneurial companies.

Which forced me to wear many hats at the same time.
IT, DB Admin, Data analysis, Financial audit, Team Lead, SL management, WF management, compliance, ISO cert, etc etc.

Though my disposition is toward data analysis.

As for your questions.

1. It's fine. Though I'd suggest using PowerQuery if you have access to it. So much easier to transform data and can go straight to data model for further analysis.

2. Nope, Ace should be able to connect to *.mdb as well. Study connection string found in link below.
https://www.connectionstrings.com/ace-oledb-12-0/

3. adOpenStatic is used to get static copy of a set of records. Meaning, additions, changes, or deletions made by other users during duration of the connection won't be visible. Read link below for more details.
https://msdn.microsoft.com/en-us/library/ee252445(v=bts.10).aspx
 
Top