ObjConnection.open gives error please help!!!!

[misterno]

It stops at the bolded line

Error says"
Run time error 3706
Provider can not be founf. It may not be properly installed."

Please help

------------------------------------------------------------------------------------------------------------
Sub ExportToAccess(rngData As Range, Optional blnHeader As Boolean = False)
Dim rstTable As New ADODB.Recordset
Dim lngRow As Long
Dim lngCol As Long
Dim strDB As String
Dim strTable As String
Dim intStartRow As Integer

If blnHeader = False Then
intStartRow = 1
Else
intStartRow = 2
End If

'--------Inputs----------
strTable = "Quotes"
'========================

' Open connection
Set objConnection = New ADODB.Connection

objConnection.ConnectionString = "provider =Microsoft.Jet.OLEDB.4.0; " & _
"data source =M:\dept\SLNGNA\Gas LPM\Structuring\Price Curves\Commercial Curves\GSGNA Commercial Curves Indication\Quote DBase\Database1.mdb;" & _
"Jet OLEDB:Database Password=koala;"

objConnection.Open

'Set rstTable = New ADODB.Recordset
Set rstTable = Nothing
rstTable.Open strTable, objConnection, adOpenDynamic, adLockOptimistic, adCmdTable

For lngRow = intStartRow To rngData.Rows.Count
With rstTable
.AddNew
For lngCol = 0 To (.Fields.Count - 1)
On Error Resume Next
.Fields(lngCol) = rngData.Cells(lngRow, lngCol + 1).Value
Next lngCol
.Update
End With
Next lngRow
Set rstTable = Nothing
objConnection.Close
Set objConnection = Nothing

End Sub

 

[jasmith4]

The best thing to do is create a blank workbook and use the Data Connection Wizard (Data, From Other Sources, From Data Connection Wizard) to try to get to the data you want manuallly -- choose Other/Advanced, then Microsoft Jet 4.0 OLD DB Provider, and go from there. If you manage to get data, check out Data, Connections, which should list your connection. Examine its Properties: go to the Definition tab, and you'll find the right connection string.

 

[misterno]

The best thing to do is create a blank workbook and use the Data Connection Wizard (Data, From Other Sources, From Data Connection Wizard) to try to get to the data you want manuallly -- choose Other/Advanced, then Microsoft Jet 4.0 OLD DB Provider, and go from there. If you manage to get data, check out Data, Connections, which should list your connection. Examine its Properties: go to the Definition tab, and you'll find the right connection string.

jasmith
I am sorry but I am not a coder

I know it sounds like a spoonfed wanna be, but I really need help

 

[jasmith4]

That's not code, it's in regular Excel. Make a blank workbook, go to the Data ribbon, click From Other Sources, and proceed from there as above. At some point it should allow you to browse to an Access database: navigate to M:\dept\SLNGNA..., choose it, and it should prompt you for a password, then present you with lists of tables and queries. Pick the one you need, click all the OK buttons, and your sheet should have data. Then on the Data ribbon click Connections, and you'll have one entry there whose Properties you should then explore to get the right connection string.

 

[misterno]

That's not code, it's in regular Excel. Make a blank workbook, go to the Data ribbon, click From Other Sources, and proceed from there as above. At some point it should allow you to browse to an Access database: navigate to M:\dept\SLNGNA..., choose it, and it should prompt you for a password, then present you with lists of tables and queries. Pick the one you need, click all the OK buttons, and your sheet should have data. Then on the Data ribbon click Connections, and you'll have one entry there whose Properties you should then explore to get the right connection string.

I got more than 1 option to choose from for OLD DB driver

analysis services 10.0
11.0
indexing service
ODBC drivers
SQL server
Simple provider

Which one to choose?

 

[jasmith4]

I got more than 1 option to choose from for OLD DB driver

analysis services 10.0
11.0
indexing service
ODBC drivers
SQL server
Simple provider

Which one to choose?

Looks like we made the same tyop -- OLE Provider.

Anyway, if you don't have Microsoft Jet 4.0 OLE DB Provider, which comes after Other/Advanced, your Office needs reinstallation.

But there's a simpler way: the Data ribbon's first button is: From Access! Do that, get some data imported into Excel, then examine Data / Connections.