VB6 - SQL Syntax - ADODB - INSERT INTO SELECT - Table in SQL Server 2012 and Table in Access database

Hi Experts,

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

Open in new window


the value of SQLCriteria is
INSERT INTO [;Database=BMBSurvey_mdb].[Sailing] SELECT * FROM Sailing IN  [Access 2003; Database=C:\SURVEYTRACKING\Survey2014.mdb;] WHERE VESSEL = 'ABC 24/14' and [Date Arrival] = '11/7/2014'

the value of DB is a connectionstring opened in ADODB which is:
db.ConnectionString
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;DataTypeCompatibility=0;Trust Server Certificate=False;Application Intent=READWRITE;

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.
gowflow
LVL 31
gowflowAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Try:
SELECT * 
INTO [;Database=BMBSurvey_mdb].[Sailing] 
FROM Sailing IN  [Access 2003; Database=C:\SURVEYTRACKING\Survey2014.mdb;] WHERE VESSEL = 'ABC 24/14' and [Date Arrival] = '11/7/2014'

Open in new window

gowflowAuthor Commented:
Thank you for your attempt.
Same result Incorrect result near the keyword IN

gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
Shouldn't be MS Access instead of Access 2003?
SELECT * 
INTO [;Database=BMBSurvey_mdb].[Sailing] 
FROM Sailing IN  [MS Access; Database=C:\SURVEYTRACKING\Survey2014.mdb;] 
WHERE VESSEL = 'ABC 24/14' and [Date Arrival] = '11/7/2014'

Open in new window

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

gowflowAuthor Commented:
Maybe as no clue and never used this but hv found this syntax on Oracle or DBase VI ... and hv replaced it by Access 2003.
Did what you suggested and still same error message on the IN.

Now I removed completely the IN to have it like this
SELECT * INTO [;Database=BMBSurvey_mdb].[Sailing] FROM Sailing  [MS Access; Database=C:\SURVEYTRACKING\Survey2014.mdb;] WHERE VESSEL = DEF 7/14' and [Date Arrival] = '10/24/2014'

and now have a different error:
The specified Schema  [;Database=BMBSurvey_mdb] either does not exist or you do not have permission to use it.

gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
Give the full path as you did with the Database=C:\SURVEYTRACKING\Survey2014.mdb
gowflowAuthor Commented:
Cannot as BMBSurvey_mdb  is the database within SQL Server 2012 !!! how can I supply the full path ?

Pls remember this is an ADODB connection here meaning the interpretation of SQL is not like Access. When I supply to a DB that is Access then the IN with full path is fine and works but here we have a database within SQL Server and frankly this is my first time attempt with this issue and really stuck.

gowflow
gowflowAuthor Commented:
I finally got to this point:

INSERT INTO BMBSurvey_mdb.dbo.Sailing SELECT * FROM Sailing [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SURVEYTRACKING\Survey2014.mdb;] WHERE VESSEL = 'ABC - 054/14' and [Date Arrival] = '10/15/2014'

is returning no error. But also returning no records.

I have tried to break the instructions to see where is the problem and noticed following:
SELECT  * FROM BSurvey_mdb.dbo.Sailing
returns the correct records.
SELECT * FROM Sailing [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SURVEYTRACKING\Survey2014.mdb;] WHERE VESSEL = 'ABC - 054/14' and [Date Arrival] = '10/15/2014'

return zero records knowing that there are records.

Kindly advise what is the syntax for the second SELECT knowing that the DB to execute is ADO on SQL server.

Regards
gowflow
however
Vitor MontalvãoMSSQL Senior EngineerCommented:
Must be the date format. Check what this return:
SELECT [Date Arrival] FROM Sailing [Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SURVEYTRACKING\Survey2014.mdb;] WHERE VESSEL = 'ABC - 054/14'

Open in new window

gowflowAuthor Commented:
Well I tried something else, I removed the WHERE clause completely and strangely enough it returns records however from the wrong table !! it ignored completely
[Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\SURVEYTRACKING\Survey2014.mdb;]

and returned the records from the Sailing data that is on SQL Server !!!! note I have the same table in both outside and inside SQL Server 'Sailing'

Even tried what you suggested and it returned zero records. I think the problem is in the fact that it is not looking for the table in the specified location. How can we make SQL Server look for a table in an access database on the disk and not in SQL Server.

Tks for sticking in with me on this !!!
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
You might need to create a Linked Server in SQL Server instance to connect to the MS Access.
gowflowAuthor Commented:
Yes I am looking at that but cannot find Microsoft.ACE.OLEDB.12.0 in the providers under Link Server what provider should I choose ? I read an article that says you have to download Microsoft Access Database Engine 2010 as SQL Server does not install the provider by default (although I have Access 2010 on my system) I went ahead and installed it and still the
Microsoft.ACE.OLEDB.12.0
does not show on the list this is what I have:

Link Server
Kindly advise
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
What about creating an ODBC for the MS Access database on the SQL Server machine?
gowflowAuthor Commented:
Not familiar at all with SQL Server and reason why I am into all this is because we need to communicate with external software that uses SQL server from our VB6 application that uses access.

So if you can help me by giving me step by step what to do I would be greatly appreciative.

Tks again
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
ODBC is on Windows level.
Here's an article that shows you how to create an ODBC for MS Access.
gowflowAuthor Commented:
ok after I create this what to do in SQL Server ?
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
Now you can create a Linked Server based on the ODBC you've created (MS OLE DB for ODBC driver).
gowflowAuthor Commented:
Well strangely enough When I opened ODBC Manager and looked in the System DSN the window was blank (although I have office 2003, 2007 and 2010 installed and VB6 ) seems when I installed SQL Server it took precedence and delete the rest ?
there was only SQL Server and SQL native client 11.0 as drivers.

I am running a repair of Office 2003 maybe it will re-instate the drivers there.

Any suggestion ?
gowflow
gowflowAuthor Commented:
No luck on the repair nothing added any clue ?
gowflow
gowflowAuthor Commented:
Oops just found it
As my OS is win 7 64bits the default ODBC Manager in control panel is a 64bit version. I looked up in SYSWOW64 directory and found the 32 bit version of ODBC manager that I ran and found all the drivers there.

Will create the link and revert with outcome.
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
As my OS is win 7 64bits the default ODBC Manager in control panel is a 64bit version.
Yes, you need to be careful with this. ODBC 32bit it's also installed on 64bit machine. Try to use only the 64bit versions.
gowflowAuthor Commented:
yes but the 64 bits does not show the drivers how to make it show then ??

this is the 64 bits version
64bits
and this is the 32 bits version
32 bits
What to do ?
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
Means that your MS Access is 32bit. You've created the DSN in ODBC Driver 32bit, right? I think you can still use it in the Linked Server.
gowflowAuthor Commented:
This is what I have in the link server what do I choose ?
and what is the rest of the fields should be updated with

link server providers
gowflwo
Vitor MontalvãoMSSQL Senior EngineerCommented:
Now you can create a Linked Server based on the ODBC you've created (MS OLE DB for ODBC driver).
It's the 5th on the list.
After that you just need to provide the Data Source and that's the name you gave to your ODBC DSN when you created it.
gowflowAuthor Commented:
I tried 2 providers OLD DB Simple and OLE DB for ODBC drivers and in both got errors.

Simple

ODBC
gowflow
gowflowAuthor Commented:
I just noticed something that could maybe be the source of the problems. The SQL Server 2012 that is installed is 64bits as it says in Add remove programs could it be the case that it is not seeing the driver in ODBC 32 ? and in ODBC 64 we don't find the mdb so what is the solution ?

Install SQL Server 2012 32 bits ??? afraid to hear a yes !!! :(

gowflow
gowflowAuthor Commented:
UPDATE

I removed the SQL Server 2012 64bits and installed SQL Server 2012 32 bits and was able in the linked server to see the ACE.12.OLE.DB provider and I successfully linked my Survey2014.mdb file with no error. and the linked server name is SURVEY2014 and have a table Sailing.

Now still my SQL which is the following returns error Invalid object name SURVEY2014.Sailing

INSERT INTO SurveyBMB_mdb.dbo.Sailing SELECT * FROM SURVEY2014.Sailing WHERE VESSEL = 'ABC  - 011/14' and [Date Arrival] = '11/13/2014'


any help is much appreciated.
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
You uninstalled the MSSQL 2012 64bit? I hope that's a test environment.
What the following select returns?
SELECT * FROM SURVEY2014.Sailing 

Open in new window

gowflowAuthor Commented:
yes for sure as it was the only application needed for hooking up to VB6 so no other SQL server instance initiated.

Well I am having different issues now.

I connected the file SurveyBMB.mdb as a linked server via this provider Microsoft.ACE.OLEDB.12.0 successfully. I see the tables and if I querry the tables I see the records in the SQL Studio Management.

Now in my VB6 application, I changed a bit my approach and simply opened a connection in ADODB and here is the connection string
Provider=Microsoft.ACE.OLEDB.12.0;Server=MyServerName;Database=SurveyBMB;Trusted_Connection=yes;

when I get to .Open in this code

'---> Open Connection
            Dim conBMB As ADODB.Connection
            Set conBMB = New ADODB.Connection
            With conBMB
                .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Server=" & sServer & ";Database=" & sBMBDatabase & ";Trusted_Connection=yes;"
                .Open
            End With

Open in new window


It throw the following error:
Could not find installable ISAMS.

Now If I change the provider to be SQL Server like this
Provider=SQLNCLI11;Server=MyServerName;Database=SurveyBMB;Trusted_Connection=yes;

I get the following error:
Cannot open database "SurveyBMB" requested by the login. login failed.

Well total lost here !!!
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
SurveyBMB is an Access or SQL Server database?
gowflowAuthor Commented:
Access database SurveyBMB.mdb that I attached as you suggested via link server in SQL Server 2012 Management Studio (this time the 32 bit version !)

gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
If it's Access you can't use a SQL Server Provider (SQLNCLI11) and neither Trusted_Connection since it's related to SQL Server only.
gowflowAuthor Commented:
ok lets go back to basic then

How cann I connect via code to linked server that is mapped or linked using this provider
Microsoft.ACE.OLEDB.12.0

I need to issues SQL instructions from within VB6 to connect to this SQL linked server how should I do it ?
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry gowflow but I may didn't see all the picture and maybe just realize it now.
Please confirm that the following statements are correct (mark and correct those that aren't):
- You have 2 MS Access databases
- You do not have any MS SQL Server database
- You're trying to use Linked Server feature to join 2 MS Access tables that are stored in different MS Access databases
- You do have a MS SQL Server user
- You do have 2 linked servers objects, each of one referring to a MS Access database
- Those 2 linked servers are working properly inside MS SQL Server
gowflowAuthor Commented:
ok here it is as I have it now

I have 2 MS Access databases Survey2014.mdb and SurveyBMB.mdb that lies on 2 separate directories. My VB6 application work fine accessing these 2 databases thru SQL Jet  instructions with DAO.database variables and DAO.recordsets.

The problem arrised as we are purchasing a new software that is developed under VB.net and this software uses SQL Server

so we created the SurveyBMB.mdb as an in between database that should connect to SQL Server so that this new software take our data from there and update the data thru the software to SurveyBMB.mdb

We were asked to connect this SurveyBMB.mdb to SQL Server so they could see it and work with it and obviously we would need also when it is connected to SQL Server for our software to be able to export and Import data to it.

Hope now the picture is clearer and sorry for having wasted your time till here as not at all familiar with SQL Server and just thrown in this for only this purpose.

To answer your questions:
YES - You have 2 MS Access databases
YES and NO - You do not have any MS SQL Server database
I had made sone searches and saw at first that I needed to use SSMA for Access to be able to link the SurveyBMB.mdb to SQL Server which in the steps you need to create an SQL Database that I called SurveyBMB_mdb and by using SSMA I linked SurveyBMB.mdb to SurveyBMB_mdb in SQL server successfully. But when I did this my application accessing SurveyBMB.mdb this way was very slow and I needed to find different ways to do it.


NO - You're trying to use Linked Server feature to join 2 MS Access tables that are stored in different MS Access databases
to clarify I don't neeed to join the 2 tables I only need SurveyBMB.mdb to be accessed from my VB6 application quickly and it to be connected to SQL Server as well.

YES - You do have a MS SQL Server user
I installed SQL Server 2012 and it uses my window authentification to access it and I also gave the use SA a password.

YES and NO - You do have 2 linked servers objects, each of one referring to a MS Access database
I have now only SurveyBMB.mdb that is linked thru link server  and no more the SSMA option described above. All I need it to know how to access via SQL instructions via DAO or Jet this linked server.

YES - Those 2 linked servers are working properly inside MS SQL Server
only 1 linked server SurveyBMB

Hope above clarifies.
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, it's more clear to me now. Thanks.

All I need it to know how to access via SQL instructions via DAO or Jet this linked server.
You can't access to SQL Server using JET. Your VB6 application and the 2 MS Access are in a different server from SQL Server and the new software?

You always need to connect to SQL Server to get access to Linked Server. And your connection string should be something similar to this:
.ConnectionString = "ODBC;DRIVER={sql server};DATABASE=" & sBMBDatabase & ";SERVER=" & sServer  & ";Trusted_Connection=Yes;"

Open in new window

gowflowAuthor Commented:

You can't access to SQL Server using JET. Your VB6 application and the 2 MS Access are in a different server from SQL Server and the new software?

They are all on same machine ! test now is on my machine and ultimately will have this scenario at the end.
SQL Server on 1 station
VB and the other 2 files on an other server.

gowlfow
Vitor MontalvãoMSSQL Senior EngineerCommented:
You're in a test environment so it's ok.
Try the connection string I posted above and tell me if it works.
gowflowAuthor Commented:
I tried your connection string posted (I kept {sql server} as you posted and did not replace by any value) the rest for sure were replaced by the content of variables. and got this error.

error
BTW I did not include in ODBC the file as you first suggested so maybe this error is due to this ?
gowflow
Anthony PerkinsCommented:
I am sure I have not read all the message back and forth, but are you trying to use JET on a 64-bit version of SQL Server?  If so, you are out of luck as there is no 64-bit version of JET.
gowflowAuthor Commented:
Well I was until I realized that all my drivers were 32 bit then I uninstalled 64 and installed 32 of SQL Server but not able to querry linked tables in SQL via VB6 using ADO.connection.

If you are able to advise a connection string that would succeed in connecting to linked server I would appreciate. Here is the latest proposal of Vitor that did not succeed with the error listed in the last thread.

.ConnectionString = "ODBC;DRIVER={sql server};DATABASE=" & sBMBDatabase & ";SERVER=" & sServer  & ";Trusted_Connection=Yes;"

Open in new window


gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
You are using ADO. You might need to use ODBC:
using System.Data.Odbc

Open in new window

gowflowAuthor Commented:
using System.Data.Odbc

is not a VB6 command !!!
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
You're right. Sorry. It was for VB.NET.
So, you can change the connection string to use the DSN you've created in ODBC:
.ConnectionString = "DSN=DSNName;Trusted_Connection=Yes;"

Open in new window

You should not need more information because everything is in the DSN configuration (driver, server name and database name).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowAuthor Commented:
Haven't yet tried it but then you are connecting thru the file in the DSN and not in SQL Server right ???

Please we are going in loops endlessly I really know and see you are a pro in SQL but you are not helping me here.

I need to clearly link to the instance that is created in SQL Server thru linked Server as the other software will be updating the database in SQL and I need for My VB6 to see the replications and not connecting thru the file that is sitting on my desktop or whatever as this I can do thru access and no need for this question altogether.

thank you
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm a SQL Server specialist but your problem is more with VB6 now. I didn't touch VB6 for ages and I'm trying to remember old stuffs to better help you.
I can see it's frustrating for you but I'm giving my best here.
gowflowAuthor Commented:
Much appreciated and sorry if u felt offended but was not my intention. Appreciate the new zone included and hope it will not discourage anyone loggingg in view the multitude of threads.

gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
Not offended at all but sharing the same frustration as you.
gowflowAuthor Commented:
Tks and much appreciated. Frustrating not to be able to find any help on this issue as hv searched extensively on the net.

The syntax when it comes to SQL Jet when opening DAO is no sweat from me but when I use ADO then you have all kind of providers and need to find the right one that able to communicate with SQL Server and here again syntax when querying also changes.

Tks for sticking with me all this time as very easy to give up especially when one of the items is looong forgotten about.

Regards
gowflow
Vitor MontalvãoMSSQL Senior EngineerCommented:
hope it will not discourage anyone loggingg in view the multitude of threads.
This can happen. Maybe is better if you can open a new question and now with more and detailed information since at least this thread helped us to get some more knowledge about this issue.
Add the actual topics to the new question and delete this one.
Anthony PerkinsCommented:
And I still don't understand why you insist on using linked servers (change SQL Server to 32 bit is a tad drastic, don't you think), so yes, I would suggest you start a new thread and explain what you are trying to achieve and why.
gowflowAuthor Commented:
TKs your effort although the connection string is fine we still have a lot to achieve the final goal reason why I posted a new question. Tks your uninterrupted help and follow.
Rgds/gowflow
gowflowAuthor Commented:
appreciate some help on the next one trying to be more precise.
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28643916.html

Rgds/gowflow
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.