using cells to run queries in excel using vb

[gceiley]

Hi, I'm very inexperienced with vb and have run into many problems in trying
to complete this task. Any help would be much appreciated.
I have an excel spreadsheet that is linked to an access 2007 db. What i'm
trying to accomplish in excel is to have the first column (id #'s) be the
parameter to generate the rest of the fields (called: Company, Bill Amount,
Payment Amount, Adjustment Amount, Payment Date, and Bill Date).
I found a similar example on another forum and tried to change that code to
fit my needs but keep encountering errors because I don't know enough about
it. Below is the code i have so far (I'm sure there are multiple errors, so
please excuse my lack of knowledge). Thank you!

Option Explicit
Public stcon As String

Sub Get_Data()
Dim stcon As String
stcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\<File Path>
;Persist Security Info=False"
Dim wbBook As Workbook
Dim wsSheet As Worksheet
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stSQL As String
Dim stPrm1 As String, stPrm2 As String
Dim j As Long

Set wbBook = ThisWorkbook
Set wsSheet = wbBook.Worksheets(1)

With wsSheet
For j = 2 To .Range("A65536").End(xlUp).Row
Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset
cnt.ConnectionString = stcon
stPrm1 = .Cells(j, 1).Value
stPrm2 = .Cells(j, 2).Value
stSQL = "SELECT dbo_Name1.COMPANY,
dbo_Subscriptions2.BILL_AMOUNT, dbo_Subscriptions2.PAYMENT_AMOUNT,
dbo_Subscriptions2.ADJUSTMENT_AMOUNT, dbo_Subscriptions2.PAYMENT_DATE,
dbo_Subscriptions2.BILL_DATE, dbo_Subscriptions2.BILL_BEGIN FROM C:\Documents
and Settings\TEST DATABASE.accdb.dbo_Name1, C:\Documents and Settings\TEST
DATABASE.accdb`.dbo_Subscriptions2 dbo_Subscriptions2 "
stSQL = stSQL & "WHERE dbo_Subscriptions2.ID = '" & stPrm1 & "'"
stSQL = stSQL & " AND dbo_Name1.ID = dbo_Subscriptions2.ID '" &
stPrm2 & "';"
cnt.ConnectionString = cnt
cnt.Open
With rst
.CursorLocation = adUseClient
.Open stSQL, cnt, adOpenStatic, adLockReadOnly
.ActiveConnection = Nothing
End With
.Cells(j, 3).CopyFromRecordset rst
rst.Close
Set rst = Nothing
cnt.Close
Set cnt = Nothing
Next j
End With

End Sub

[Cor Ligthert[MVP]]

Your question has nothing to do with the vb here meant and also not with
directX

Try an office forum or an answer forum

http://social.answers.microsoft.com/Forums/en-US/categories

Success

Cor

"gceiley" <gce...@discussions.microsoft.com> wrote in message
news:17AA879A-1086-4540...@microsoft.com...