2

I am trying to access a SQL database using embedded SQL in VBA. The problem is in defining the conditions of what I want to access ( ID > @Identifie is the part causing the problem). When I run the code, I get Must declare the scalar variable @Identifie. However when I go to my locals window, @Identifie is correctly declared and has the value I assigned in cell K6. Moreover, if I replace @Identifie in my condition (ID > @Identifie) by a certain value, my code runs perfectly, thus there are no other errors. It seems that my problem comes from the append method, but I can't figure out what i'm doing wrong.

Here is my code:

(I removed the connection string in this post, but this was clearly not the problem):

Option Explicit
Option Base 1

Sub LoadData()

Application.ScreenUpdating = False

Dim cn As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim cmd As New ADODB.Command

Dim lastID As Double

Dim parametre As New ADODB.Parameter

Dim Last_total_ID As Double

Last_total_ID = ActiveWorkbook.Worksheets("Consolidated").Range("K6").Value

cn.ConnectionString = ""
cn.Open

Set parametre = cmd.CreateParameter(Name:="@Identifie", Type:=adDouble, Direction:=adParamInput)

parametre.Value = Last_total_ID

cmd.Parameters.Append parametre

cmd.ActiveConnection = cn

cmd.CommandText = "Select ID, Issuer,LaunchDate,SettleDate,CADAmount,Description,Price,Currency, Maturity,IssuerID, Coupon FROM dbo.tblHistoricalIssuanceStats WHERE (IsProvy = 1) AND (ID > @Identifie)"

Set rs = cmd.Execute
Cœur
  • 37,241
  • 25
  • 195
  • 267
Patrick Benga
  • 21
  • 2

1 Answers1

0

Why not:

cmd.CommandText = "Select ID, Issuer,LaunchDate,SettleDate,CADAmount,Description,Price,Currency, Maturity,IssuerID, Coupon FROM dbo.tblHistoricalIssuanceStats WHERE (IsProvy = 1) AND (ID > " & Last_total_ID & ");"

I don't think you need to use an ADODB parameter.

The Mad Pope
  • 1
  • 2