Excel & Access & SQL

[Hlatigo]

Hello everyone,

I am trying to learn how to connect to access w/o using the ado controls. Reason why is because at work, the system they have wont allow me to add those connections without some liscence and I cant get.

What I was hoping was for someone to tell me how would I connect to access using SQL and extract and add data to access from excel. I am just starting on this project and so I was just hoping for some generic answers and ideas. I have done several searches but never really seem to understand what is being done. The closest i got was one from NateO and I really dont get it. I have posted his code below but not sure if this works.

BUT, from what he writes, it uses ADO and thats no good for me. Thanks for any help.


Sub Import()
Dim cn As Object, rs As Object, myCalls As String
Dim MySql As String, dbfullname As String, myCnt As Long
dbfullname = "P:\DATA\TestUpdate.mdb"
myCalls = "22" 'SQL Variable
MySql = "SELECT [BC_Calls], [talk], [work] " & _
"FROM tblMONTHLY_BASELINE WHERE " & _
"[BC_Calls]='" & myCalls & "';" 'Stack your SQL string
myCalls = Empty 'Clear SQL variable string

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" _
& dbfullname & ";" 'Create DB connection

Set rs = CreateObject("ADODB.Recordset")
With rs
Set .ActiveConnection = cn
.Source = MySql 'Pass your SQL
.Open , , 3, 3 '.Open , , adOpenStatic, adLockOptimistic
myCnt = .RecordCount
If myCnt > 0 Then
.MoveLast: .MoveFirst
'Pull data to first sheet, cells a1:RecordestCountRow & column 3 _
3 fields in the sql pass
Sheets(1).Range(Cells(1, 1), Cells(myCnt, 3)).CopyFromRecordset rs
End If
.Close
End With
cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub

 

[NateO]

There's not a chance that will work without a Jet OLE DB Provider. But, you're looking in the wrong place. Click, in the VBE, on Help->About Microsoft Visual Basic...System Info...->Office X Applications->Microsoft Office X Environment->OLE DB Providers.

Also see:

http://msdn.microsoft.com/library/en-us/oledb/htm/oledbprovjet_overview.asp

And you might be interested in:

http://msdn.microsoft.com/library/en-us/vbcon98/html/vbconusingdatagridcontrol.asp

http://www.thescarms.com/vbasic/ExcelImport.asp

 

[Hlatigo]

Thanks so very much, I will give it a shot