SQL creates linked table in front end. (1 Viewer)

exaccess

Registered User.
Local time
Today, 15:22
Joined
Apr 21, 2013
Messages
287
Hello Experts,
I have a frontend and a backend database. As usual i keep the tables in the backend. Now I have some tables which need to be emptied and then refilled. I empty the table using the code
Code:
DoCmd.RunSQL "DELETE * FROM TransNMTbl;"
then I fill in the table using
Code:
DoCmd.RunSQL "INSERT INTO TransNMTbl SELECT * FROM TransTbl;"
The code runs fine. The problem is that the linked table is not used and the sql statement creates another TransNMTbl table in the front end and puts the data there. Bu I need the table to be in backend. Help please.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 10:22
Joined
Jan 23, 2006
Messages
15,362
DELETE * from Table
only deletes the records from the Table. It does Not DELETE the Table itself.

You could use

Code:
DoCmd.RunSQL "DROP Tble TableName;"
 
Last edited:

RainLover

VIP From a land downunder
Local time
Tomorrow, 01:22
Joined
Jan 5, 2009
Messages
5,041
It should work if it is linked.
 

exaccess

Registered User.
Local time
Today, 15:22
Joined
Apr 21, 2013
Messages
287
DELETE * from Table
only deletes the records from the Table. It does Not DELETE the Table itself.
Yes but assuming that Table is the table_name isn't that exactly what I am using?
 

RainLover

VIP From a land downunder
Local time
Tomorrow, 01:22
Joined
Jan 5, 2009
Messages
5,041
If "Table" is the name you are using you are using a reserved word. Please change it to MyTable or something that suits your design style.

Other Reserved words are,

Date
Now
If
Then
Form
Report etc.

Do a Google on RESERVED words for more information.

You should also find information in our archives.
 

exaccess

Registered User.
Local time
Today, 15:22
Joined
Apr 21, 2013
Messages
287
If "Table" is the name you are using you are using a reserved word. Please change it to MyTable or something that suits your design style.
No I am not using the reserved word Table. Let me give more info on the problem:
In backend I have TransSHTbl and TransUPDSHTbl that are linked to frontend. In frontend in a module I use the code:
Code:
DoCmd.RunSQL "DELETE * FROM TransSHTbl;"
DoCmd.RunSQL "INSERT INTO TransSHTbl SELECT * FROM TransTbl;"
This table stays in Backend.
But the table TransUPDSHTbl which is filled in by the code
Code:
INTSQL = "SELECT TransSHTbl.FirstName, TransSHTbl.LastName, TransSHTbl.Language, TransSHTbl.Email, " & _
            " TransSHTbl.Federation " & _
            " INTO TransUPDSHTbl " & _
            " FROM TransSHTbl " & _
            " INNER JOIN MembersTbl ON ((TransSHTbl.LastName = MembersTbl.NOM) AND " & _
            " (TransSHTbl.FirstName = MembersTbl.PRENOM));"
DoCmd.RunSQL INTSQL
gets unlinked and created in the frontend. I want the TransUPDSH to stay in the backend too.
 
Last edited:

Minty

AWF VIP
Local time
Today, 14:22
Joined
Jul 26, 2013
Messages
10,354
INSERT INTO SELECT will add rows to an existing table (As per your first example)

SELECT INTO will create a new table. (As per you second example)
Change the second SQL Query around and you get the result you want.
 

exaccess

Registered User.
Local time
Today, 15:22
Joined
Apr 21, 2013
Messages
287
Re: SQL creates linked table in front end.SOLVED

INSERT INTO SELECT will add rows to an existing table (As per your first example)

SELECT INTO will create a new table. (As per you second example)
Change the second SQL Query around and you get the result you want.

I have done what you said. The problem is solved. Here is the result. Many thanks.
Code:
"INSERT INTO TransUPDSHTbl SELECT MembersTbl.MemID, TransSHTbl.FirstName, TransSHTbl.LastName, " & _
            " TransSHTbl.Language, TransSHTbl.Email, TransSHTbl.Federation " & _
            " FROM TransSHTbl " & _
            " INNER JOIN MembersTbl ON ((TransSHTbl.LastName = MembersTbl.NOM) AND " & _
            " (TransSHTbl.FirstName = MembersTbl.PRENOM));"
 

Users who are viewing this thread

Top Bottom