How to make Excel recognize ".accbd" files?

[rugbier]

Greetings!

Well this is my first post and I'm not a native speaker, so if asomething isn't clear enough, just ask me...

I have a code in Excel to export data from Access, but apparently it doesn't recognize ".accbd" as a valid extension for an Access file.

The code is:

Sub ExtraiVarias()


Dim Conex As ADODB.Connection
Dim RS As ADODB.Recordset
Dim Diretorio As String
Dim Col As Integer
Dim Lin As Integer
Dim sSQL As String
Dim COD_BD As Integer
Dim DataInicial As Date


Range("A11:z1200").ClearContents


DataInicial = Range("B5").Value
DataFinal = Range("B6").Value


Range("A11").Value = DataInicial


Lin = 11


While Cells(Lin, 1).Value <> DataFinal


    Cells(Lin + 1, 1).FormulaR1C1 = "=EDATE(R[-1]C,1)"
    
    Lin = Lin + 1
    
Wend


Diretorio = "V:\BD_MACRO_teste.accdb"


Col = 2


Set Conex = New ADODB.Connection


With Conex


    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Open Diretorio
    
End With


While Not IsEmpty(Cells(8, Col))


COD_BD = Cells(8, Col).Value


 Set RS = New ADODB.Recordset
 RS.CursorLocation = adUseServer




sSQL = "SELECT  VALOR"
sSQL = sSQL & " FROM DADOS"
sSQL = sSQL & " WHERE COD_BD=" & COD_BD & "And Data >= #" & DataInicial & "#" & "And Data <= #" & DataFinal & "#" & ";"




RS.Open Source:=sSQL, ActiveConnection:=Conex, CursorType:=adOpenStatic, _
     LockType:=adLockReadOnly
            


Cells(11, Col).CopyFromRecordset RS


RS.Close


Col = Col + 1


Wend


Conex.Close
            
End Sub

The error message is: "Unrecognized database format 'V:\BD_MACRO_teste.accdb'", and appears in the line ".Open Diretorio".

I presume that I need to locate the necessary reference in the library tha allows me to use this kind of files, because it works when i save the same database as an Access 2003 file (".mdb"), but i don't know how to find it. Any new idea will be really helpful.

Thanks in advance,

 

[martindwilson]

from here http://social.msdn.microsoft.com/Forums/pl-PL/vblanguage/thread/30d14dcd-eb8a-4856-b4d4-f50c4d32c606

Access 2007 use a different data base format. You need to download the AccessDatabaseEngine and install it on your target machine.​

http://www.microsoft.com/downloads/...36-8C28-4598-9B72-EF94E038C891&displaylang=en

And then,
a) Deselect Microsoft DAO 3.6 object library
b) Select Microsoft Office 12 access database engine object library​

 

[rugbier]

Thanks for the answer!

Unfortunately, as I am using VBA, this probably won't work. Besides, I'm using ADO, and Microsoft DAO 3.6 object library is already not selected. I probably need to find the correct library in my PC, but I can't figure out which one.

Does anyone have another suggestion?

Thanks in Advance!

 

[martindwilson]

have you tried it?

 

[rugbier]

Found the answer here!

New engine (ACE), try "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=MyDatabaseName.accdb;"

So, i just nedd to change the Provider of the ADODB Connection


Well, thanks anyway! Hope it's useful!