[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

How do I execute a append query with a paramenter using ado?

I have a global ado connection I call goconn in my unbound vb app.
I also have several append queries in my Access database that I would like to execute using ado.
How do I execute these querys using parameters?

Thanks in advance,

Sheri
0
Sheritlw
Asked:
Sheritlw
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Hi Sheri,

With "qryTest" in NorthWind.mdb having two parameters, "FName" & "LName"...

 Dim conn As ADODB.Connection
 Dim cmd As ADODB.Command
 Dim rs As ADODB.Recordset
 Dim paramFname As Parameter
 Dim paramLname As Parameter

 Set conn = New ADODB.Connection
 Set cmd = New ADODB.Command
 
 conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\NorthWind.mdb; Jet OLEDB;"

 Set cmd.ActiveConnection = conn

 cmd.CommandText = "qryTest"
 cmd.CommandType = adCmdStoredProc

 Set paramFname = cmd.CreateParameter("FName", adVarChar, adParamInput, 10, "a")

 cmd.Parameters.Append paramFname

 Set paramLname = cmd.CreateParameter("LName", adVarChar, adParamInput, 20, "")

 cmd.Parameters.Append paramLname

 Set rs = cmd.Execute(, cmd.Parameters)

' etc.


BFN,

fp.
0
 
[ fanpages ]IT Services ConsultantCommented:
FYI:

More background reading...

"How To Call a Parameterized Query to an Access Database with ADO"
[ http://support.microsoft.com/kb/q200190/ ]

"How To Invoke a Parameterized ADO Query Using VBA/C++/Java"
[ http://support.microsoft.com/kb/q181734/ ]

BFN,

fp.
0
 
SheritlwAuthor Commented:
Tried this below and it ignored the parameter and appened all records into the table


goconn  is a global adodb.connection
sWO = "123ab"

Set cmd = New ADODB.Command
            Set cmd.ActiveConnection = goConn
            cmd.CommandText = "Archive_Schedules"
            cmd.CommandType = adCmdStoredProc
            Set paramFname = cmd.CreateParameter("WorkOrderNo", adVarChar, adParamInput, 10, sWO)
            cmd.Parameters.Append paramFname

 I was using
 sql = "Archive_Job_Details where workorderno = '" & sWO & "'"
 goConn.Execute sql, , adExecuteNoRecords

but is was also appending all records
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
[ fanpages ]IT Services ConsultantCommented:
Sorry, what does "Archive_Job_Details" do?

Please post the SQL statement associated with this (query?).

BFN,

fp.
0
 
SheritlwAuthor Commented:
Both Archive_Schedules and Archive_Job_Details are names of append querys.

The Archive_Job_Details statement is just an example of what I used previously except that the statement was

sql = "Archive_Schedules Where workorderno = '" & sWO & "'

0
 
wraith821Commented:
open the append query in the database and get the SQL text from it. Copy that into you application ad call the
goconn.execute "INSERT INTO blah blah blah WHERE Field=" & parameter_variable_holding_Value_you_want_to_pass
0
 
SheritlwAuthor Commented:
I used to do that but really want to avoid it.
0
 
[ fanpages ]IT Services ConsultantCommented:
Define your Append query ("qryAppend") as follows:

INSERT INTO [TABLE_NAME] ([WORK_ORDER_NUMBER]) SELECT WorkOrderNo

Then use this code to add a record to [TABLE_NAME] with the [WORK_ORDER_NUMBER] column set to the parameter "WorkOrderNo":

Dim conn As ADODB.Connection
 Dim cmd As ADODB.Command
 Dim rs As ADODB.Recordset
 Dim Work_Order_Number As Parameter

 Set conn = New ADODB.Connection
 Set cmd = New ADODB.Command
 
 conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\NorthWind.mdb; Jet OLEDB;"

 Set cmd.ActiveConnection = conn

 cmd.CommandText = "qryAppend"
 cmd.CommandType = adCmdStoredProc

 Set paramFname = cmd.CreateParameter("WorkOrderNo", adVarChar, adParamInput, 10, "a")

 cmd.Parameters.Append Work_Order_Number

 Set rs = cmd.Execute(, cmd.Parameters)


BFN,

fp.
0
 
SheritlwAuthor Commented:
I'm assuming your Work_Order_Number is the name of the field?   My field name is WorkOrderNo and the value of the parameter is held in a string named sWO.

My database query is...

INSERT INTO tblArchiveSchedules ( ApptID, HColumn, JobsToScheduleID, JobsMainID, ApptDate, EndDate, OverNight, DayOfWeek, StartTime, EndTime, BatchSession, NextStartTime, NextEndTime, DriveStartTime, DriveEndTime, Remind, ApptText, AppendedNotes, DriveText, DriveCheck, DriveApptCode, Crew, DriveTime, ChangedText, TimeLock, Leader, DayNumber, DayTimeOff, DateEntered, TimeEntered, ExtraNameField1, ExtraField1, ExtraNameField2, ExtraField2, ExtraNameField3, ExtraField3, ExtraNameField4, ExtraField4, ExtraNameField5, ExtraField5, ExtraNameField6, ExtraField6, ExtraNameField7, ExtraField7, ExtraNameField8, ExtraField8, ExtraNameField9, ExtraField9, ExtraNameField10, ExtraField10 )
SELECT tblSchedules.ApptID, tblSchedules.HColumn, tblSchedules.JobsToScheduleID, tblSchedules.JobsMainID, tblSchedules.ApptDate, tblSchedules.EndDate, tblSchedules.OverNight, tblSchedules.DayOfWeek, tblSchedules.StartTime, tblSchedules.EndTime, tblSchedules.BatchSession, tblSchedules.NextStartTime, tblSchedules.NextEndTime, tblSchedules.DriveStartTime, tblSchedules.DriveEndTime, tblSchedules.Remind, tblSchedules.ApptText, tblSchedules.AppendedNotes, tblSchedules.DriveText, tblSchedules.DriveCheck, tblSchedules.DriveApptCode, tblSchedules.Crew, tblSchedules.DriveTime, tblSchedules.ChangedText, tblSchedules.TimeLock, tblSchedules.Leader, tblSchedules.DayNumber, tblSchedules.DayTimeOff, tblSchedules.DateEntered, tblSchedules.TimeEntered, tblSchedules.ExtraNameField1, tblSchedules.ExtraField1, tblSchedules.ExtraNameField2, tblSchedules.ExtraField2, tblSchedules.ExtraNameField3, tblSchedules.ExtraField3, tblSchedules.ExtraNameField4, tblSchedules.ExtraField4, tblSchedules.ExtraNameField5, tblSchedules.ExtraField5, tblSchedules.ExtraNameField6, tblSchedules.ExtraField6, tblSchedules.ExtraNameField7, tblSchedules.ExtraField7, tblSchedules.ExtraNameField8, tblSchedules.ExtraField8, tblSchedules.ExtraNameField9, tblSchedules.ExtraField9, tblSchedules.ExtraNameField10, tblSchedules.ExtraField10
FROM tblSchedules RIGHT JOIN tblJobs ON tblSchedules.JobsMainID = tblJobs.JobsMainID;
0
 
[ fanpages ]IT Services ConsultantCommented:
Thanks for the SQL.

Yes, [Work_Order_Number] is the column name I used.

I cannot see where you are using "WorkOrderNo" in your query.

Passing this as a parameter will have no effect.

BFN,

fp.

0
 
[ fanpages ]IT Services ConsultantCommented:
Well... to be completely biased, Gerry, I believe I answered the question as far as I could without further input from the asker.

If/when they return, I will continue, but in the meantime, some appreciate of my effort would be welcome! :)

Thanks for your time.

BFN,

fp.

0
 
GPrentice00Commented:
A rather nice touch using a working, available to all, database to demonstrate.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now