Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Replacing a table to append to in SQL.

Posted on 2006-06-23
11
Medium Priority
?
246 Views
Last Modified: 2008-03-06
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.
0
Comment
Question by:Carl2002
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 1500 total points
ID: 16967008
Hi Carl2002,
 qdf.SQL = Replace(ssql, "XXX", "Main Extract " & rs1!TradingGroup)


Pete
0
 
LVL 4

Author Comment

by:Carl2002
ID: 16967015
Hi Pete

I tried that and get a message that says:

Syntax error in INSERT INTO statement

Carl.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16967053
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
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.

 
LVL 26

Expert Comment

by:dannywareham
ID: 16967059
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
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16967062
qdf.SQL = Replace(qdf.SQL, XXX, "Main Extract " & rs1!TradingGroup)
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 16967076
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 16967179
Good Morning Carl :)
0
 
LVL 4

Author Comment

by:Carl2002
ID: 16967351
Morning Rocki
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16967406
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
 
LVL 4

Author Comment

by:Carl2002
ID: 16967411
You are right rockiroads, pete deserves the lot
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16967485
The last bit of this thread has lost me, but thanks anyway.

Pete
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question