Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
connect to Access 2007
In 2005, thanks to code posted on this board, I created a workbook
which connects to Access & retrieves data from recordsets. The code works fine, except now when I try to use it with Access 2007 I get the error: "Unrecognized database format 'C:\...\myfile.accdb' Is there a way to update the code so it will work with Access 2007? Thanks in advance for any help! Here is the relevant section: Public cnMinistry As ADODB.Connection Private Function ConnectToDatabase() As Boolean 'instantiate the connection & connect Set cnMinistry = New Connection cnMinistry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strFileToOpen & ";" cnMinistry.Open '<--- error happens here If cnMinistry.State = adStateOpen Then ConnectToDatabase = True End Function References: Visual Basic for Applications Microsoft Excel 11.0 Object Library OLE Automation Microsoft Office 11.0 Object Library Microsoft Forms 2.0 Object Library Microsoft.ActiveX Data Objects 2.5 Library Some more code that may have to change: Private Sub ImportAccessTable(cn As ADODB.Connection, TableName As String, TargetRange As Range) Dim rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) Set rs = New ADODB.Recordset rs.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable For intColIndex = 0 To rs.Fields.Count - 1 ' the field names TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data rs.Close Set rs = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
connect to Access 2007
I don't know if it's related to the problem or not, but you may need to
change your references to point to the Excel 12 and Office 12 libraries. Those libraries may be available on PCs which have been upgraded from Office 2003 to Office 2007, but will probably not be available on computers with only Office 2007. To check your connection string, try the following: In Excel, start recording a macro. Make a manual connection to the database (I use Excel 2003, so the menu may be different, but something like Data - Import External Data - New Database Query). Go through the steps to create a query, but it doesn't matter what the query is as we're only checking the connection string, so just choose any field from an appropriate table. Once the data has been returned to Excel, stop the macro recording and look at what's been recorded. Does the connection string match what you have in your macro? I'm guessing that if the database has been upgraded to Access 2007, then the provider needs to change. "Dave B" wrote: In 2005, thanks to code posted on this board, I created a workbook which connects to Access & retrieves data from recordsets. The code works fine, except now when I try to use it with Access 2007 I get the error: "Unrecognized database format 'C:\...\myfile.accdb' Is there a way to update the code so it will work with Access 2007? Thanks in advance for any help! Here is the relevant section: Public cnMinistry As ADODB.Connection Private Function ConnectToDatabase() As Boolean 'instantiate the connection & connect Set cnMinistry = New Connection cnMinistry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strFileToOpen & ";" cnMinistry.Open '<--- error happens here If cnMinistry.State = adStateOpen Then ConnectToDatabase = True End Function References: Visual Basic for Applications Microsoft Excel 11.0 Object Library OLE Automation Microsoft Office 11.0 Object Library Microsoft Forms 2.0 Object Library Microsoft.ActiveX Data Objects 2.5 Library Some more code that may have to change: Private Sub ImportAccessTable(cn As ADODB.Connection, TableName As String, TargetRange As Range) Dim rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) Set rs = New ADODB.Recordset rs.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable For intColIndex = 0 To rs.Fields.Count - 1 ' the field names TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data rs.Close Set rs = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
connect to Access 2007
try using
Provider=Microsoft.Jet.OLEDB.12.0 You might read through these article: http://support.microsoft.com/kb/247412 http://support.microsoft.com/kb/246335 Also check the name of your access file extension. MDB might be something like ACDB -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Dave B" wrote: In 2005, thanks to code posted on this board, I created a workbook which connects to Access & retrieves data from recordsets. The code works fine, except now when I try to use it with Access 2007 I get the error: "Unrecognized database format 'C:\...\myfile.accdb' Is there a way to update the code so it will work with Access 2007? Thanks in advance for any help! Here is the relevant section: Public cnMinistry As ADODB.Connection Private Function ConnectToDatabase() As Boolean 'instantiate the connection & connect Set cnMinistry = New Connection cnMinistry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strFileToOpen & ";" cnMinistry.Open '<--- error happens here If cnMinistry.State = adStateOpen Then ConnectToDatabase = True End Function References: Visual Basic for Applications Microsoft Excel 11.0 Object Library OLE Automation Microsoft Office 11.0 Object Library Microsoft Forms 2.0 Object Library Microsoft.ActiveX Data Objects 2.5 Library Some more code that may have to change: Private Sub ImportAccessTable(cn As ADODB.Connection, TableName As String, TargetRange As Range) Dim rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) Set rs = New ADODB.Recordset rs.Open TableName, cn, adOpenStatic, adLockOptimistic, adCmdTable For intColIndex = 0 To rs.Fields.Count - 1 ' the field names TargetRange.Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name Next TargetRange.Offset(1, 0).CopyFromRecordset rs ' the recordset data rs.Close Set rs = Nothing End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
connect to Access 2007
"Tom Ogilvy" wrote:
try using Provider=Microsoft.Jet.OLEDB.12.0 Also check the name of your access file extension. MDB might be something like ACDB This connection string worked for me: "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myDatabase.accdb;" Hope this helps. -- urkec |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I connect to access 2007 data from excel? | Excel Discussion (Misc queries) | |||
Excel VAB+ADO connect ACCESS with SQL | Excel Programming | |||
Connect to MS Access with Password via VBA | Excel Programming | |||
Help with using ADO to connect Excel to Access | Excel Programming | |||
ADO Connect Access | Excel Programming |