PDA

View Full Version : connection access Excel(VBA)



leo13
11-24-2006, 09:23 AM
Hello,

I can`t realise the connexion betwen access(2000) and excel(2000).
when execute the code i would have a bug.



Private Sub ConnecterBase(ConnectBD As Object, Optional rs As Object, Optional pa As Object)
Set ConnectBD = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set pa = CreateObject("ADODB.Recordset")
With ConnectBD
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = chemin_de_base_donnees
.Open
End With
End Sub


the probleme it's in the provider.
thanx for your help.

CBrine
11-24-2006, 10:00 AM
Leo,
Here's the code I use.


Dim oConn As ADODB.Connection
Dim sSql As String
Dim Rs As ADODB.Recordset
Set oConn = New ADODB.Connection
Set Rs = New ADODB.Recordset
oConn.Open _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=c:\documents and settings\" & Environ("UserName") & "\My Documents\TPM Database.mdb"
sSql = "Select * from dbo_Storedata"
Rs.Open sSql, oConn
Range("A1").CopyFromRecordset Rs
oConn.Close


Just change the dbq path to your database path.

HTH
Cal

Ken Puls
11-24-2006, 03:38 PM
Private Sub ConnecterBase(ConnectBD As Object, Optional rs As Object, Optional pa As Object)
Set ConnectBD = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set pa = CreateObject("ADODB.Recordset")
ConnectBD.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & chemin_de_base_donnees & ";"
End Sub


The above is assuming that chemin_de_base_donnees holds the full path to your database (including the .mdb extension). I don't see it passed into or set in the sub at all though.

EDIT: I have only provided (untested) what should make the connection. I haven't looked at any of the rest of this for best practices.

HTH,