Solved

Replacing a table to append to in SQL.

Posted on 2006-06-23
11
239 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views 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 Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

809 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