SQLTeam.com | Weblogs | Forums

Need Help with SQL Query in VBA


#1

Hello everyone,

Using VBA, I am trying to store data into a variable with a
simple SQL query. However, the variable is returning "False" instead of
the data in the table. Below is my code. Is there a syntax error in
my SELECT Statements? Thank you in advance for your
help!

Sub QueryRevRec_tblAudit()

Dim tblAuditConn As ADODB.Connection
Dim tblAuditData As ADODB.Recordset
Dim tblAuditField As ADODB.Field
Dim Counter As Integer
Dim SourceFile As String
Dim Perspective As String

Set tblAuditConn = New ADODB.Connection
Set tblAuditData = New ADODB.Recordset

SourceFile = "'FileName'"

tblAuditConn.ConnectionString = ConStrSQL

For Counter = 1 To 50

tblAuditConn.Open

On Error GoTo CloseConnection

With tblAuditData

    .ActiveConnection = tblAuditConn

Perspective = .Source = "SELECT Perspective " & _
                "FROM tblProcessStep " & _
                "WHERE SourceFile = " & SourceFile & " AND ProcessStepNo = " & Counter & ";"

#2

what is the value of Perspective variable. can you do a windows.message or whatever it is in VB