Solved

Replacing a table to append to in SQL.

Posted on 2006-06-23
11
237 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 500 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now