Using Excel and SQL
it isn't access, it is SQL server not that probably makes much difference.
"Joel" wrote:
There are a number of ways of getting data from Access. It depends if it is
ADO vs DBO and if you want to perform a query, get a table, or use SQL
language. I'm not sure how familar you are with SQL but here is a simple
method.
The SQL statement needs to be modified "Select * from [Case]"
The SQL statement so this statment is a sting and can be modified to include
where
"Select * from [postcodes] where ([postodes] like 10001"
or
for RowCount = 1 to 100
Zipcode = Range("A" & Rowcount)
"Select * from [postcodes] where ([postodes] like " & Zipcode
next RowCount
for code below to run you need to add reference libraries to you VBA window
menu
Tools - References
check the following items
Microsoft Access 11.0 Ojbect Library
Microsoft ActiveX Data Objects 2.8 library
Press OK.
Use the latest version of the library on your system.
--------------------------------------------------------------------------------------------
Option Explicit
Sub TestOne()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Long
Dim myrow As String
ThisWorkbook.Sheets(3).Cells.ClearContents
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
ThisWorkbook.Path & "\Test.mdb"
rs.Open "Select * from [Case]", cn
Do While Not rs.EOF
For i = 1 To 10
If rs.EOF = True Then
Exit For
End If
myrow = rs.GetString(StringFormat:=adClipString, _
numrows:=1, ColumnDelimeter:=",")
ThisWorkbook.Sheets(3).Cells(i, 1) = myrow
rs.MoveNext
Next i
Loop
rs.Close
cn.Close
End Sub
"scubadiver" wrote:
I have a list of postcodes in Excel and I want to use VBA to get information
from a table in SQL server which has almost 2million rows.
I would really like to know what code I need to use to get the required
records to match the postcodes.
Does that make sense?
"Joel" wrote:
There seems to be an error in you r lastt posting. Can't read the message.
"Joel" wrote:
The simpliest thig to do is to record a macro when performing the opration
manually.
1) Start Recording : Tools - Macro - Record New Macro
2) get data : Data - Import External Data - New Database Query. Browse for
the Server location and select the Table from the database.
3) Stop recording : Tools - Macro - Stop Recording.
You can get the macro by typing Alt-F11 from worksheet and looking in the
Modules portion of the Project window. the recorded macro can be manually
changed as required. If you need additional help post the recorded macro.
"scubadiver" wrote:
Hello,
I have a list of postcodes in Excel and I want to match them using an SQL
table from a warehouse server
I have been told that I can use VBA programming but what code do I use or
can I be pointed in the right direction?
cheers
|