INSERT INTO [;DATABASE=D:\rommelfiles\Database2.accdb].table1 ( ID, Fn, Mn )
SELECT table1.ID, table1.Fn, table1.Mn
FROM table1 LEFT JOIN [;DATABASE=D:\rommelfiles\Database2.accdb].table1 AS vDestination ON table1.ID = vDestination.ID
WHERE vDestination.ID Is Null;
wow this is already perfect.
but I have just this question. why the different syntax of append query from the usual one?
and another thing is that when I edit the sql again, the "].destionation" part changes back to ") destination" Is there something wrong here. It is to be stated that I have saved it before I edited it.
Is there other way of writing its syntax. where can I find site for learning those syntax?
another thing is that, Do I need to specify the fields or can I just use the * asterisk sign on the Select statement and in the Insert Into fields?
can you tell me datAdrenaline where you learn the above code syntax for INSERT INTO because I think its different from the original synta so I can understand why and how it works. thank you Sir.
anyway, when I tried that inside the module, it doesnt work.
Because Access formats the SQL Statment into an invalid SQL statment. To show this, create your query with the valid syntax ...
INSERT INTO [;DATABASE=D:\rommelfiles\Database2.accdb].[Destination] ( ID, Fn, Mn )
SELECT Source.ID, Source.Fn, Source.Mn
FROM Source
LEFT JOIN [;DATABASE=D:\rommelfiles\Database2.accdb].[Destination] As vDestination
ON Source.ID = vDestination.ID
WHERE vDestination.ID Is Null;
Now ... save and close the query object ... Now ... open it up in Design View. it will look like this:
INSERT INTO (;DATABASE=D:\rommelfiles\Database2.accdb) Destination ( ID, Fn, Mn )
SELECT Source.ID, Source.Fn, Source.Mn
FROM Source
LEFT JOIN (;DATABASE=D:\rommelfiles\Database2.accdb) Destination As vDestination
ON Source.ID = vDestination.ID
WHERE vDestination.ID Is Null;
So ... now IF YOU DON'T CHANGE IT ... it runs fine because Access is running the "Optimized" version that is stored out of site ... but ... just put a space somewhere between two word, which shoud not effect anything, now... try to run the query again and you will get a syntax error... thus the reason I call Access's behavior a "BUG". Any program that takes VALID syntax, and reformats it to INVALID syntax has bug in it! ...
It is for this reason that I will generally use the syntax I proposed in VBA with programatically generated SQL statements for use with something like a CurrentDb.Execute or CurrentDb.OpenRecordset.
......
>> where you learn the above code syntax for INSERT INTO <<
Hmmm ... its not so much the INSERT INTO since you can use a similar syntax in the other types of SQL statements too (ie: SELECT or UPDATE). But .. the question is where I learned it? ... well ... I more or less discovered it in the sense that for SQL Server the generic SQL statement is something like:
SELECT * FROM server.database.owner.table
Or basically:
SELECT * FROM datasource.table
So ... I knew that linked tables used the .Connect property to link to tables, so I thought that "hey ... lets try the .Connect property as the datasource" and tried it out against a SQL Server back end with something like:
SELECT * FROM [ODBC;<other settings>].MyTable
Then ... WOW! ... it worked! ... Then I got confirmation, if you will, from a fellow MVP (Doug Steele -- I think) by reading a post of his on a private news group that used that syntax.
The cool thing about this syntax is that you can JOIN two or more tables from completely different sources ... the JET db engine is quite powerful! ...
In addition, you can use any valid .Connect string (or in hooking into a JET db, you simply use the full name and path of the file). You can even use this syntax to open/import CSV files....
Code:
Public Sub ImportCSVFile()
Dim strSQL As String
strSQL = "SELECT * INTO tblDestinationTableName"
" FROM [Text;FMT=Delimited;HDR=YES;CharacterSet=437" & _
";DATABASE=C:\CSVFolderPath].[CSVFileName.txt] AS vMyCSV;"
CurrentDb.Execute strSQL, dbFailOnError
End Sub
While that article tells you what you could use as the "header" argument (ie: Text or ODBC or etc.) it does NOT go into the additional arguments you will need... so I will often just create a linked table to a similar source, then analyze the .Connect property of the linked table as well as any DSN that was created as a result, then start playing!
I am guessing that if you used a feature of Access called .ShowPlan ... I have not view a .ShowPlan output for these (or similar) types of query objects.
Essentially it is a linked table on the run. Fantastic.
Such a query deserves a name. It seems half way to a Pass-Through.
Perhaps Pass-By. Though I like the sound of Drive-By for the minimalist involvment with the remote database.
So pleased I got involved in this thread. You just don't find out how stuff like this works except when someone shares their experience.
