Replacing a table to append to in SQL.

I have some sql code which I can paste if need be but the crooks of the matter are it begins with this line

INSERT INTO XXX ( BranchNo, BranchName, TradingGroup, OccupantName, District, JobCode, [Month], RequestedAt, dbo_Jobs_Description, Asset, ConfidentialNotes, SubAsset, [Job Status], Name, Reactive, TotalCount, CancelledCount, [Status Category], Period, Priority )

Where XXX is the table to append, I want to be able to replace this with something else dependant on where the code is in its looping cycle

I have this line

qdf.SQL = Replace(ssql, XXX, "Main Extract " & rs1!TradingGroup)

But it doesn't replace XXX it ignores the replace and tries to append to table XXX (which doesn't exist)

Any ideas anyone ? I'm desperate !!! - LOL

Carl.
LVL 4
Carl2002Asked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
Hi Carl2002,
 qdf.SQL = Replace(ssql, "XXX", "Main Extract " & rs1!TradingGroup)


Pete
0
 
Carl2002Author Commented:
Hi Pete

I tried that and get a message that says:

Syntax error in INSERT INTO statement

Carl.
0
 
peter57rCommented:
Then you have a different problem.

Put a
Debug.print qdf.sql
immediately after the replace command a so you can see what the statement then says.

You must have "xxx" though.

Pete
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
dannywarehamCommented:
How about loading your table names into an array, then looping through that?

Dim myTable as String(3)   ' for 4 tables, for example
Dim i as Integer
Dim mySQl as String
myTable(0) = "tblName1"
myTable(1) = "tblName2"
'etc

For i = 0 to 3

mySQL = "INSERT INTO " & myTable(i) & " ( BranchNo, BranchName, TradingGroup, OccupantName, District, JobCode, [Month], RequestedAt, dbo_Jobs_Description, Asset, ConfidentialNotes, SubAsset, [Job Status], Name, Reactive, TotalCount, CancelledCount, [Status Category], Period, Priority )"

Next i

This is just an example....
0
 
Rick_RickardsCommented:
qdf.SQL = Replace(qdf.SQL, XXX, "Main Extract " & rs1!TradingGroup)
0
 
Rick_RickardsCommented:
Another possibility is that you're not requiring variables to be declared explicity by having the...

Option Explicit

Declared at the beginning of the module.  If this is the case it may thing that XXX is merely a variable not the string "XXX" and if so this should turn do the trick...

qdf.SQL = Replace(ssql, "XXX", "Main Extract " & rs1!TradingGroup)

Good Luick,

Rick
0
 
rockiroadsCommented:
Good Morning Carl :)
0
 
Carl2002Author Commented:
Morning Rocki
0
 
rockiroadsCommented:
Carl

This was a question raised from another question

http://www.experts-exchange.com/Databases/MS_Access/Q_21895648.html

It has now been answered, so I guess you might want to close this.

I hope the other experts don't mind me saying this but

If u feel anyone here has contributed to helping you solve it, you should award them points, there are different grading levels also. Otherwise you can post a request in Community Support to close this.


Pete, u were right on there, just needed the square brackets around the table name, as it has spaces in

0
 
Carl2002Author Commented:
You are right rockiroads, pete deserves the lot
0
 
peter57rCommented:
The last bit of this thread has lost me, but thanks anyway.

Pete
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.