I have been struggling with this issue for sometime now and tried all searches on the web and can't see a solution I am getting a syntax error 'near the IN clause' and need help on this.
I know the syntax of SQL when it comes to SQL Jet but here as I am accessing data on SQL server I am using ADODB connection and here is my syntax and need help on it.
I get Incorrect syntax near the keyword IN.
my code is as follows:
Dim SQLCriteria As String
Dim lRecAffected As Long
SQLCriteria = "INSERT INTO [;Database=" & "" & sToDBName & "].[" & sTable & "]"
SQLCriteria = SQLCriteria & " SELECT " & sFields & " FROM " & sTable & " IN [Access 2003; Database=" & sFmDBName & ";]"
SQLCriteria = SQLCriteria & " WHERE VESSEL = '" & sVessel & "' and [Date Arrival] = '" & sDate & "'"
If sOrderby <> "" Then SQLCriteria = SQLCriteria & " ORDER BY " & sOrderby
DB.Execute SQLCriteria, lRecAffected
the value of SQLCriteria is
INSERT INTO [;Database=BMBSurvey_mdb].
[Sailing] SELECT * FROM Sailing IN [Access 2003; Database=C:\SURVEYTRACKING
4.mdb;] WHERE VESSEL = 'ABC 24/14' and [Date Arrival] = '11/7/2014'
the value of DB is a connectionstring opened in ADODB which is:
Provider=SQLNCLI11.1;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=MyServerName;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeC
Appreciate any help in this syntax as totally helpless. For your info variable sToDBName points to the database on SQL server. and sFmDBName is as shown on the example the full name and path of the Access mdb database.
I would need the syntax to work in both direction ie
1) In this case it is INSERT INTO (table in SQL Server) SELECT FROM (table in outside Access mdb)
2) In an other case INSERT INTO (table in Access mdb) SELECT FROM (table in SQL Server)
Thanks in advance for your help.