Solved

Replacing a table to append to in SQL.

Posted on 2006-06-23
11
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: 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!

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

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…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

726 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