Import data from axcess using sql

phillypdh

Board Regular
Joined
Jun 23, 2011
Messages
66
Code:
Sub SrchTrackingNums()
Dim Con As ADODB.Connection
Dim strSQL As String
Dim strCon As String
Dim strVariable As String
Dim Cell As Range
Dim Rs As ADODB.Recordset
Dim rCount As Long, Crec As Long
strCon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=z:\Support\ Master.accdb"
rCount = Range("E2").End(xlDown).Row - 1
For Each Cell In Range("E2", Range("E2").End(xlDown))
    Crec = Cell.Row - 1
    Application.StatusBar = Format(Crec / rCount, "0.00% Completed")
    strVariable = Cell.Value
 
    strSQL = "SELECT [Loan_Number],[LOB],[PACKAGE_TYPE],[RequestDateTime] " & _
        "FROM [Master] " & _
        "WHERE [FedExTrackToConsumer] = '" & strVariable & "';"
 
    Set Rs = New ADODB.Recordset
    Rs.Open strSQL, strCon, adOpenForwardOnly, adLockReadOnly, adCmdText
    Cell.Offset(0, 3).CopyFromRecordset Rs
    Rs.Close
    Crec = Crec + 1
Next Cell
Application.StatusBar = ""
Set Rs = Nothing
End Sub

the macro begins and then just stops responding?
 
Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,226,284
Messages
6,190,053
Members
453,591
Latest member
plengeb

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