ADO Connection

[dhartford]

Help is needed. Thanks in advance.

I have the following code to transfer data from Access to Excel. I received error msg: "Data source name not found and no default driver specified". Data source name is correct with correct path:



Private Sub cmdSubmit_Click()
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

Dim strConn As String

strConn = "Microsoft.Jet.OLEDB.4.0" & _
"Data source = F:\View\Viewer_fname.xlsx;" & _
"Extended Properties=Excel 12.0 Xml;HDR=YES"

cnn.Open strConn

Debug.Print strConn
End Sub

[shamsam1]

from reference add dao 3.6 library and Microsoft office 11/12 object library..hope this will solve ur problem

or another smile methode to do

Access 2003 VBA Export Code Example:
Export an Access Table to an Excel Sheet:

'Behind an Access VBA Form

Private Sub Command1_Click()
Application.DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "C:\Book1.xls", False, "Sheet2$"
End Sub
or

Option Explicit

'Add a reference to MS ActiveX Data Objects 2.x Library

Private Sub Command1_Click()

Dim oRs As ADODB.Recordset

Dim oCnn As ADODB.Connection

Dim i As Integer

'Connect to your Access db

Set oCnn = New ADODB.Connection

oCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=name.mdb;User Id=;Password=;"

oCnn.Open

'Create your recordset

Set oRs = New ADODB.Recordset

oRs.Open "SELECT * FROM Table1;", oCnn, adOpenKeyset, adLockReadOnly, adCmdText

'Add to your current workbook and add the field names as column headers (optional)

For i = 0 To oRs.Fields.Count - 1

Workbooks("Book1").Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name

Next

Workbooks("Book1").Sheets(1).Range("1:1").Font.Bold = True

Workbooks("Book1").Sheets(1).Cells(2, 1).CopyFromRecordset oRs

'Clean up ADO Objects

oRs.Close

Set oRs = Nothing

oCnn.Close

Set oCnn = Nothing

End Sub

[stanl]

strConn = "Microsoft.Jet.OLEDB.4.0" & _
"Data source = F:\View\Viewer_fname.xlsx;" & _
"Extended Properties=Excel 12.0 Xml;HDR=YES"


You are mixing 2003 and 2007 - Try



strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data source = F:\View\Viewer_fname.xlsx;" & _
"Extended Properties=Excel 12.0 Xml;HDR=YES"


Stan

[Dr.K]

Arrrggggg!

The Jet Engine has been replaced in 2007?
Looks like I've got some reading to do.

[dhartford]

Stan,

Thank you very much for your comments. I've tried following code and got same error msg.




strConn = "Microsoft.ACE.OLEDB.12.0;" & _
"Data source = C:\ReViewer_Composite.xlsx;" & _
"Extended Properties=Excel 12.0 Xml;HDR=YES"

[CreganTur]

Arrrggggg!

The Jet Engine has been replaced in 2007?
Looks like I've got some reading to do.

Yes, JET has received an upgrade for working with the new Office 2007 file formats. If you're going to be working with accdb., xlsx, docx, etc. (and are using Access 2007) then you would use Microsoft.Ace.oledb.12.0

If you're using Access 2007 and you want to work with a 97-2003 file format, you would use the standard Microsoft.Jet.oledb.4.0 connection.

I'm not sure what to do if you're in Access 2003 and want to connect to an Office 2007 file format...:think:

[Dr.K]

I'm not sure what to do if you're in Access 2003 and want to connect to an Office 2007 file format...:think:

You kill the IT department for rolling out 2007 too early. ;)

Thanks for filling me in.

[stanl]

I've tried following code and got same error msg.


My guess then is you don't have Office 2007 installed and are trying to open a 2007 file with 2003, in which case you need to download the compatibility drivers from Microsoft [free download], which will install the Ace 12 OLEDB provider.

However, don't hold you breath about updating an .xlsm file from Access in Compatibility mode : pray2: Stan

[dhartford]

My guess then is you don't have Office 2007 installed and are trying to open a 2007 file with 2003, in which case you need to download the compatibility drivers from Microsoft [free download], which will install the Ace 12 OLEDB provider.

However, don't hold you breath about updating an .xlsm file from Access in Compatibility mode : pray2: Stan

Stan,

I have only office 2007 installed on my machine. Really want to find out what I can do to proceed my projuct.

Thanks.

[Mavyak]

I've had things error out on me when I don't add a semi-colon to the end of the connection string. Perhaps adding a semi-colon after 'YES' in your connection string will solve the issues. Just brainstorming.

note: I also noticed that there is no semi-colon after '4.0' in your first connection string which would case the provider and data source arguments to combine into one.

[dhartford]

I've had things error out on me when I don't add a semi-colon to the end of the connection string. Perhaps adding a semi-colon after 'YES' in your connection string will solve the issues. Just brainstorming.

note: I also noticed that there is no semi-colon after '4.0' in your first connection string which would case the provider and data source arguments to combine into one.

Thank you for pointing out. Here is my strConn now and same error received.



strConn = "Microsoft.ACE.OLEDB.12.0;" & _
"Data source = F:\fname.xlsx;" & _
"Extended Properties=Excel 12.0 Xml;HDR=YES;"

[Mavyak]

According to this page:

http://www.connectionstrings.com/?carrier=excel2007

The Extended Properties portion of the connection string should be encased in its own set of double-quotes. Perhaps something like the following will work(?) :

strConn = "Microsoft.ACE.OLEDB.12.0;" & _
"Data source = F:\fname.xlsx;" & _
"Extended Properties=" & Chr(34) & "Excel 12.0 Xml;HDR=YES" & Chr(34) & ";"

[dhartford]

According to this page:

http://www.connectionstrings.com/?carrier=excel2007

strConn = "Microsoft.ACE.OLEDB.12.0;" & _
"Data source = F:\fname.xlsx;" & _
"Extended Properties=" & Chr(34) & "Excel 12.0 Xml;HDR=YES" & Chr(34) & ";"

I tried different combination of string and still not works. (((

[CreganTur]

I don't know about the Extended Properties part of this... but I do know that you cannot have spaces separating the equals sign used in a connection string.

Hopefully this will help you:


strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data source=F:\fname.xlsx;" & _
"Extended Properties=" & Chr(34) & "Excel 12.0 Xml;HDR=YES" & Chr(34) & ";"


NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: I also changed this connection string to explicitly declare Provider- implicit declaration may be causing some of your problems.

[Dr.K]

Ug. As much as I LOVE ADO connections and SQL, if all you are doing is transfering data, you might be better off using the Excel Object model directly.

Just create an appXL object, and then use appXL.CopyFromRecordset to dump the data in.