Executing dynamic SQL in Access97

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Executing dynamic SQL in Access97

Author

Topic

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-03-20 : 11:39:29

I am using Access97 with a SQL7 backend. The following code is simply (if only) trying to INSERT a record into a table. The SQL is dynamic (see below). The problem i have is it says 'expected parameters 3' . How do i pass the values of three controls on a form into the values part of the strSQL below ?

Dim qdfBemcoRanking As QueryDef
Dim strSQL As String

Set db = CurrentDb

strSQL = "INSERT INTO Plc_Bemco_Ranking ( fi_plcQaireID, fi_bemcoCode, fd_score ) " & _
"VALUES(me.fl_plcQaireID, me.cbo_BemcoList, me.cbo_RankList)"

Set qdfBemcoRanking = db.CreateQueryDef("BemcoRanking", strSQL)

qdfBemcoRanking.Execute
Set qdfBemcoRanking = Nothing

many thanks for any pointers
Paul

Love like you've never been hurt,
Dance like no one is watching,
Sing like no one is listening.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-20 : 12:25:10

It's the quotes that are throwing you off. You have to build your statement so the values of the variables get passed rather than the name of the variable. Something like this:

strSQL = "INSERT INTO Plc_Bemco_Ranking ( fi_plcQaireID, fi_bemcoCode, fd_score ) " & _
"VALUES(" & me.fl_plcQaireID & ", " & me.cbo_BemcoList & ", " & me.cbo_RankList & ")"

And for any values that are strings, you have to add single quotes (') so if me.cbo_BemcoList is a string, it would look like

"VALUES(" & me.fl_plcQaireID & ", '" & me.cbo_BemcoList & "', " & me.cbo_RankList & ")"

------------------------
GENERAL-ly speaking...

Edited by - AjarnMark on 03/20/2002 12:27:34

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-21 : 01:38:51

and from memory - if you do
Set qdfBemcoRanking = db.CreateQueryDef("", strSQL)

then your query is only a temporary querydef and you wont have to worry about cleaning it up later....

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-03-21 : 06:02:24

Still getting a problem....

I get a 'run-time error 3155 ODBC INSERT on linked table Plc_Bemco_Ranking failed' at the qdfBemcoRanking.execute line (see below). There are no constraints/triggers that would cause the insert not to happen. Does anyone have an idea what may be causing this error ? many thanks.

Set db = CurrentDb

strSQL = "INSERT INTO Plc_Bemco_Ranking ( fi_plcQaireID, fi_bemcoCode, fd_score ) " & _
"VALUES(" & Me.fi_plcQaireID & "," & Me.cbo_BemcoList & "," & Me.cbo_RankList & ")"

Set qdfBemcoRanking = db.CreateQueryDef("", strSQL)

qdfBemcoRanking.Execute

Further to the above. The error also occurs when trying the insert this way...

Set qdfBemcoRanking = db.QueryDefs("qry_bemcoRankingAdd")
Set rsBemcoRanking = qdfBemcoRanking.OpenRecordset(dbOpenDynaset, dbAppendOnly + dbSeeChanges)

With rsBemcoRanking
.AddNew
!fi_plcQaireID = Me.fi_plcQaireID
!fi_bemcoCode = Me.cbo_BemcoList
!fd_score = Me.cbo_RankList
.Update
End With
rsBemcoRanking.Close

I CAN however type the record directly into the linked table ??

Love like you've never been hurt,
Dance like no one is watching,
Sing like no one is listening.

Edited by - knookie on 03/21/2002 06:52:42

KnooKie
Aged Yak Warrior

623 Posts

Posted - 2002-03-21 : 07:12:16

It's working now. Something strange is happening on my machine. The above error is occurring because i seem to lose permission to INSERT into the table ?? I've no idea quite why though. I close and re-open the access DB and it worked fine ? I'm not using any permissioning in Access at all ????????

Love like you've never been hurt,
Dance like no one is watching,
Sing like no one is listening.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources