How to append query without error (1 Viewer)

genesis

Registered User.
Local time
Today, 06:39
Joined
Jun 11, 2009
Messages
205
I have been using this code below in a query and it works just fine.

code:
INSERT INTO (;DATABASE=D:\files\Database2.accdb) table1 ( ID, Fn, Mn )
SELECT table1.ID, table1.Fn, table1.Mn
FROM table1 LEFT JOIN (;DATABASE=D:\files\Database2.accdb) table1 AS vDestination ON table1.ID = vDestination.ID
WHERE vDestination.ID Is Null;
however when I use this on module level the above code does not work. it says syntax error. so I modified it below in the module..

dim insertinto as string

insertinto = "INSERT INTO Table1 ( ID, Fn, Mn ) IN 'D:\rommelfiles\Database2.accdb' " _
& "SELECT Table1.ID, Table1.Fn, Table1.Mn " _
& "FROM Table1 LEFT JOIN table1 IN 'D:\rommelfiles\Database2.accdb' AS vDestination ON table1.ID = vDestination.ID" _
& "WHERE vDestination.ID Is Null;"
still syntax error on From Clause;

any other way to revise the code to make it work?

I need the code to be in module level.

Kindly help me solve this code.
 
Last edited:

namliam

The Mailman - AWF VIP
Local time
Today, 15:39
Joined
Aug 11, 2003
Messages
11,696
add a debug.print to your code and find your error

Tip: You are missing a space !
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:39
Joined
Aug 11, 2003
Messages
11,696
To prevent this problem dont use line continuations, instead use this:
mySQL = ""
mySQL = mySQL & " Select ... "
mySQL = mySQL & " From ... "
mySQL = mySQL & " Join ... "
mySQL = mySQL & " where ... "

NOTE: the space in front of the key words to prevent your problem
+ This kind of syntax works always, where line continuations are limited
+ This keeps your sql readable thus maintainable
 

genesis

Registered User.
Local time
Today, 06:39
Joined
Jun 11, 2009
Messages
205
namliam, I am using Access 2007. I cannot test with debug.print now because I am in an internet cafe researching for this code.

maybe you can lead me what is the proper syntax of insert into and left join so that I can properly modify the code.

the first code was assisted me by datAdrenalin. I can seem to get where he got that syntax.
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:39
Joined
Aug 11, 2003
Messages
11,696
OMG, man your missing a space I told you so already. Even if I give you the fixed code without access in an internet cafe you cannot test it anyways....

You are missing a space, check in detail your code or use the debug.print solution
Tip: It is at the end of a line.... and you can solve it by using the notation I already recomended!
 

genesis

Registered User.
Local time
Today, 06:39
Joined
Jun 11, 2009
Messages
205
ok. thanks namliam. I will try it at home. but one more question.

the two codes above have different syntax.

the first has

INSERT INTO (;DATABASE=D:\files\Database2.accdb) table1 ( ID, Fn, Mn )

the second has

INSERT INTO Table1 ( ID, Fn, Mn ) IN 'D:\rommelfiles\Database2.accdb'

do you possible know why there is that different syntax? I have been googling to find the answer but I cannot find the first syntax type. always the second type syntax is available in the internet.
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:39
Joined
Aug 11, 2003
Messages
11,696
I dont deal with cross database queries, instead use linked tables to take care of the linking... reducing the queries to something I dear to understand.

I can imagine as to the how or why... *grasping in the air*
The (;DATABASE=D:\files\Database2.accdb) notation is the notation used in linking tables, while the IN 'D:\rommelfiles\Database2.accdb' notation is for referencing a "foreign" database.
Not sure how they tie into eachother or not.... but the IN seems to be the proper notation from doing a quick test on my own (2002) database.
It might even be that the one notation is needed in one version vs another, I just dont know, am doing speculated guessing.
 

genesis

Registered User.
Local time
Today, 06:39
Joined
Jun 11, 2009
Messages
205
sorry. i havent read this last night namliam because it was already 12 midnight and I have to go because the internet cafe is already close at that time. but thanks for the reply.

I did try the debug.print but it didnt work so I used msgbox err.description to ge the error message and it states the error in FROM clause still using the two codes above.
 

genesis

Registered User.
Local time
Today, 06:39
Joined
Jun 11, 2009
Messages
205
namliam, actually it is my second option to link the table from externaldatabase if I cannot find the solution.

so now I did opt for the second option. however still my append query has error message saying key violations. the error was only proper because the two tables have primary , unique keys. therefore, if table1 has "animal" record and table2 also has "animal" record, and I will append to table2 then the error appears.

it was so funny that docmd.setwarnings false will do the trick. I never thought of that.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:39
Joined
Sep 12, 2006
Messages
14,510
after you assisng the insertinto string, do a msgbox to see what you have.

as mailman says, sql is unforgiving of punctuation, so you need to be sure you have spaces bewtten commands. too many spaces is better than none - so if you arennt sure insert a space

now how are you running the query - this code fragment should help identify any syntax error

on error to fail
currentdb.execute sqlstrg, dbfailonerror

exit sub

fail:
msgbox "error: " & err & " desc: " & err.description
 

genesis

Registered User.
Local time
Today, 06:39
Joined
Jun 11, 2009
Messages
205
thanks for replying Dave. Using the setwarnings did do the trick. I finally finish the synchronization code for access 2007 (accdb format) that I will use in my projects.
 

namliam

The Mailman - AWF VIP
Local time
Today, 15:39
Joined
Aug 11, 2003
Messages
11,696
I did try the debug.print but it didnt work so I used msgbox err.description to ge the error message and it states the error in FROM clause still using the two codes above.

What didnt work, your brain??
Debug.print prints out the sql statement so you can find the missing space.

Alternatively re-write your SQL into my suggested notation/format and fix your problem. You are missing a frigging space if you use your brain and look at your code you will see there is one place there is missing a space...
 

dfenton

AWF VIP
Local time
Today, 09:39
Joined
May 22, 2007
Messages
469
In case you didn't get the point of "you're missing a space", the place you are missing it is between the end of the FROM clause and the beginning of the WHERE clause:

Code:
insertinto = "INSERT INTO Table1 ( ID, Fn, Mn ) IN 'D:\rommelfiles\Database2.accdb' " _
& "SELECT Table1.ID, Table1.Fn, Table1.Mn " _
& "FROM Table1 LEFT JOIN table1 IN 'D:\rommelfiles\Database2.accdb' AS vDestination ON table1.ID = vDestination.ID" _
& "WHERE vDestination.ID Is Null;"

The last line should be changed to:

Code:
& " WHERE vDestination.ID Is Null;"

...or the line before it should have a trailing space added:

Code:
& "FROM Table1 LEFT JOIN table1 IN 'D:\rommelfiles\Database2.accdb' AS vDestination ON table1.ID = vDestination.ID " _

I'm posting this for others who come along later, as much for you, since I don't see from reading the thread that anyone pointed out the actual error.
 

datAdrenaline

AWF VIP
Local time
Today, 08:39
Joined
Jun 23, 2008
Messages
697
Hello Genesis ...

I have shown you in your previous similar threads as to a proper syntax you need ...

Specifically here:
http://www.access-programmers.co.uk/forums/showthread.php?p=902813#post902813

... and the fact that Access 2007 has a bug with reformatting the proper SQL statement into one that is incorrect (just like the one you posted) ...

Here is the syntax that I use as it is accurate, flexible, and correct ... as well as the one I posted in your other threads :).
Code:
INSERT INTO [;DATABASE=D:\files\Database2.accdb].[table1] ( ID, Fn, Mn )
SELECT table1.ID, table1.Fn, table1.Mn
FROM table1 LEFT JOIN [;DATABASE=D:\files\Database2.accdb].[table1] AS vDestination ON table1.ID = vDestination.ID
WHERE vDestination.ID Is Null;

To do this in VBA you simply build the above string ... NOT the one Access coerces it to when you save the above SQL statement as a query object ...

Code:
DIM strInsertInto As String
 
strInsertInto = "INSERT INTO [;DATABASE=D:\files\Database2.accdb].[table1] ( ID, Fn, Mn )" & _
                " SELECT table1.ID, table1.Fn, table1.Mn" & _
                " FROM table1 LEFT JOIN" & _
                     " [;DATABASE=D:\files\Database2.accdb].[table1] AS vDestination" & _
                     " ON table1.ID = vDestination.ID" & _
                " WHERE vDestination.ID Is Null;"
 
CurrentDb.Execute strInsertInto, dbFailOnError
 
Last edited:

genesis

Registered User.
Local time
Today, 06:39
Joined
Jun 11, 2009
Messages
205
thanks for replying still on this one Sir Brent but I used setwarnings to false to trap the error that way. i dont know if that is proper way of doing it but the works fine for me.
 

datAdrenaline

AWF VIP
Local time
Today, 08:39
Joined
Jun 23, 2008
Messages
697
You're welcome ... but ... your reply does not seem to make sense?

DoCmd.SetWarnings False

Will not suppress the errors generated by CurrentDb.Execute. The code and SQL I provided is correctly syntaxed and the SQL you posted originally is incorrectly syntaxed (even if you add the space) .

So .... just curious as to what is and is not working for you? ...

-------------

Just trying to make sure the advice is understood, plus fixes your issue!...
 
Last edited:

genesis

Registered User.
Local time
Today, 06:39
Joined
Jun 11, 2009
Messages
205
I used the code at VBA level. The SQL that you provided works but I did not used the

currentdb.execute but instead I used the docmd.setwarnings.
 

dfenton

AWF VIP
Local time
Today, 09:39
Joined
May 22, 2007
Messages
469
I used the code at VBA level. The SQL that you provided works but I did not used the

currentdb.execute but instead I used the docmd.setwarnings.

This is gibberish. DoCmd.SetWarnings does not execute a SQL DML statement. Using .Execute DoCmd.SetWarnings would have no effect at all, so you must be using DoCmd.RunSQL. In that case, if you use DoCmd.SetWarnings FALSE, your SQL will not execute at all, as it's invalid in the first place (unless you've corrected the original missing space somewhere along the line).

You should avoid RunSQL as it forces you to turn off messages so you don't bother the user, but it also tacitly executes SQL without reporting errors. That is, for instance, if you have a SQL statement inserting 10 records, and 1 of them has an error, instead of notifying you, it will tacitly append the 9 records and the one with the error will be ignored.

I have repeatedly posted in various forums my SQLRun() function that is a replacement for DoCmd.RunSQL (once you add my code to your project, you can just search and replace "DoCmd.RunSQL" with "SQLRun") because my SQLRun() function addresses the drawbacks of DoCmd.RunSQL while being no more difficult to use (in fact, it's easier since you don't have to turn off messages, and it also provides additional functionality absent in RunSQL, such as returning the number of records affected).
 

genesis

Registered User.
Local time
Today, 06:39
Joined
Jun 11, 2009
Messages
205
I did not use the currentdb.executre strinsertinto, dbfailonerror to trap any error.

I use the docmd.setwarnings false at the beginning of the code that is to be executed to trap error therein and not as part of the sql statement dfenton sir.
 

Users who are viewing this thread

Top Bottom