Excel & Access & SQL

H

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Are you using Excel97/Access97 by any chance? That's the only reason that i can see that you wouldn't be able to use the CopyFromRecordset method under ADO. You can however use DAO (another library which will let you manipulate the data in much the same way).

Regards

Richard
 
Upvote 0
Can't you just goto Data>Get External Data...?
 
Upvote 0
Hello,
Hlatigo said:
I can use ADO but i can't use things like recordset. sorry
Click to expand...
I'm assuming you're using Office Pro if you're using Access and Excel. Go into Tools->References, if you see a library for for 'Microsoft ActiveX Data Objects 2.X Library', you can use ADO including Recordsets. The ADO Recordset Object is native to this library.

That code needs to be changed quite a bit. Chances are you don't have the following DB on your system: 'P:\DATA\TestUpdate.mdb'. Which would also mean you don't have a field named 'BC_Calls', etc...

You'll also want a Jet OLE DB provider on your system, chances are, it's there.
 
Upvote 0
Hello everyone!

I have read on DAO and in the book I am reading it states that I should always use ADO since it is more flexible, it also states that with ADO I could use DAO to manipulate the databse. But that is where my problems lies. I was hoping to get started with learning SQL so I can begin working on other non-access related databases. DAO is Access specific, from my understanding. I am using office XP professional

I didnt want to do a MSquery, Norie. You have taught me alot about that already and it works awesome, but I was just hoping to make a connection using SQL and starting with access as my database since I assume it would be easier to learn that way.


Hello NateO...I have just read something on jet and I do have Microsoft ActiveX Data Objects 2.8 Library but when i try to place a recordset into my userform that is when a window pops up and says "control could not be created because it is not properly licensed."
 
Upvote 0
An ActiveX Control license issue is different than an ADO Recordset issue, yes?

Try by passing the Recordset to a Spreadsheet first, to be sure. ;)

I don't think the problem is an ADO issue, what kind of Control are you using?
 
Upvote 0
I am not sure what the difference is, I kinda assume they are one of the same package and both are needed in order to scroll though a database.

I have gone through the control box in excel and added the following controls:

Adodc
Datacombo
DataGrid
Datalist
datarepeater

Than I went into the VB portion of excel and opened up the toolbox there and added them because they were not there at first. I can't drag any of them into a spreadsheet and not will they show up in the toolbox within excel. I am kinda lost on this issue

So I guess this leads me back to trying to connect to access with SQL if that is possible. Is there any code out there generic enough that it would apply to alomost any simple access database?

thanks!
 
Upvote 0
The code above will do that, just pass the recordset to a Range, i.e.,

Code: 
Sub Import()
Dim cn As Object, rs As Object
Dim MySql As String

Let MySql = "SELECT * from Table1"

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\temp\test.mdb"

Set rs = CreateObject("ADODB.Recordset")

With rs
    .Open MySql, cn, 3, 3
    Sheets(1).Range("a1").CopyFromRecordset rs
    .Close
End With

cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub
The CopyFromRecordset Method applies to a Range object, not a Data Grid control:

http://msdn.microsoft.com/library/en-us/vbaxl11/html/xlmthCopyFromRecordset1_HV05200141.asp

Microsoft said:
expression.CopyFromRecordset(Data, MaxRows, MaxColumns)

expression Required. An expression that returns a Range object.
Click to expand...
If you want to work with a Data Grid, you'll need to iterate through it, CopyFromRecordset won't work. However, you still want to work with a Recordset.
 
Upvote 0
Thats awesome, I will try.

I have been trying to look for Microsoft.Jet.OLEDB.4.0 in tools/references but I dont see that as an option. I do have Microsoft jet and replication objects 2.6 library but somehow I dont think this will work.

any thoughts if this line of code will work w/o the jet file?

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\temp\test.mdb"
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Adodb Method 'Open' of object '_Recordset' failed
Replies
4
Views
639
Micron
M
C
  • Question
What libraries can be used to access SQL Server databases using Excel/VBA on a Mac?
Replies
0
Views
218
chuckbo
C
S
  • Solved
Accessing Sharepoint List from Excel/VBA using ADODB connection
Replies
3
Views
3K
RoryA
RoryA
A
  • Question
Recording Excel ranges from several tabs into Access DB
Replies
1
Views
364
alansidman
A
S
  • Question
Automation error Unspecified error
Replies
0
Views
415
Suavesav
S

Forum statistics

Threads
1,215,372
Messages
6,124,541
Members
449,169
Latest member
mm424

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top