VB6/ADO - Linked Server MSAccess - SQL Server 2012 32 bits - SQL Syntax

If I have understood correctly you want to insert into a table in your Access mdb file from your VB6 program, but you can't do so directly. You already have the Access database Survey2014.mdb set up as linked server 'SurveyBMB' in MSSQL.

I have to say that I've never personally created a linked server to an MSAccess database, but the syntax to address a linked server is 4 part notation (LinkedServer.Database.Schema.Objectname). The middle sections of the 4 part name can sometimes be empty if they are the default or only object in that namespace. You can explore the object hierarchy for your linked server in SSMS.

SurveyBMB...Survey

Regarding the connection string, you would be connecting to the MSSQL server, so there wouldn't be another separate connection string.

If I have understood correctly you want to insert into a table in your Access mdb file from your VB6 program, but you can't do so directly. You already have the Access database Survey2014.mdb set up as linked server 'SurveyBMB' in MSSQL.

You understood it reverse.

INSERT INTO
SurveyBMB that is an isnstance created as link server in SQL Server 2012
and
SELECT * FROM
C:\SURVEYTRACKING\Survey2014.mdb is an MS Access database.

BOTH SurveyBMB and C:\SURVEYTRACKING\Survey2014.mdb are both Access databases but we had to link SurveyBMB.mdb to SQL Server so that the other software developed in VB.NET could communicate with it.

Hope above clarifies.

Re your explanation I am aware of the 4 parts and have revised my syntax as rember that I had successfully connected before to the link table with dots and here is my new syntax

INSERT INTO SurveyBMB...Survey SELECT * FROM Survey IN "C:\SURVEYTRACKING\Survey2014.mdb" WHERE Survey.VESSEL = 'ABC - 058/14' and Survey.[Date Arrival] = '11/19/2014' ORDER BY Item

I get the following error:


Can you please help ?
gowflow

Hi gowflow.
You might want to change the LinkedServer name so is confusing with the Access database name (I made the same confusion myself in the previous question).

About the T-SQL, you can try to use the OPENDATASOURCE statement:

INSERT INTO SurveyBMB.Survey 
SELECT * 
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="C:\SURVEYTRACKING\Survey2014.mdb"')...Survey
WHERE Survey.VESSEL = 'ABC 24/14' and Survey.[Date Arrival] = '11/7/2014' 
ORDER BY Survey.Item

Select allOpen in new window

I don't think so, different error here is the string

INSERT INTO SurveyBMB...Survey SELECT * FROM OPENDATASOURCE(Provider='Microsoft.Jet.OLEDB.4.0','Data Source="C:\SURVEYTRACKING\Survey2014.mdb"').Survey WHERE Survey.VESSEL = 'ABC - 058/14' and Survey.[Date Arrival] = '11/19/2014' ORDER BY Item

I have tried with
... ="C:\SURVEYTRACKING\Survey2014.mdb"')...Survey
and without the word Provider and all give following error.


BTW I also changed the linked server name completely and called it KSGBMB and still same error.
Pls advise
gowflow

It's really as I pasted. Can you try without the INSERT clause to see if the error persists?

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="C:\SURVEYTRACKING\Survey2014.mdb"')...Survey WHERE Survey.VESSEL = 'ABC 24/14' and Survey.[Date Arrival] = '11/7/2014' ORDER BY Item

Same error
gowflow

What do you have configured in SQL Server for  'Ad Hoc Distributed Queries'?
You can see that by running the following command: sp_configure 'Ad Hoc Distributed Queries'

If returns 0 then run the following script:

sp_configure 'show advanced options',1   
GO
reconfigure with override   
GO   

sp_configure 'Ad Hoc Distributed Queries',1   
GO
reconfigure with override   
GO

Select allOpen in new window

And the run the SELECT again.

ok total newbie in SQL Server.

I press on New Querry and pasted your line
sp_configure 'Ad Hoc Distributed Queries'

Which showed 'Ad Hoc Distributed Queries' in Red and pressed Execute button and got following result:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.

gowflow

I don't know maybe this could help you this is the structure that I have in SQL Server for this database just the name has been changed to KSGBMB


gowflow

First make sure that you have linked Access database correctly. In sql server management studio, navigate to the linked server, and verify that you can expand its object down to the table you want, and you don't receive any errors.
On this screenshot, DB is the name of the linked server and Table1 is a table in Access database.

Once this is done, right-click the table in the linked server, and generate INSERT script, then modify it for your application. It probably will be INSERT INTO SurveyBMB...Survey

@Anthony
Value of running the SQL you posted
SELECT *
FROM sys.configurations
WHERE name = 'Ad Hoc Distributed Queries'

returned Value = 0
Pls advise what to do now ?

@Vadim
The problem lies in VB6 to communicate with the link server. I have the same screenshot that you have and my access database was linked successfully. My problem is that I need to run the INSERT INTO from within VB6 and not on the Querry screen this is software that need to update data constantly.

gowlfow

I press on New Querry and pasted your line
 sp_configure 'Ad Hoc Distributed Queries'

 Which showed 'Ad Hoc Distributed Queries' in Red and pressed Execute button and got following result:
 Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
 The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.

You need to execute all my script since the first part is to put in advanced option so you can check the value in the 2nd part. Since you already know that's zero then you need to set it to 1:

sp_configure 'show advanced options',1   
GO
reconfigure with override   
GO   

sp_configure 'Ad Hoc Distributed Queries',1   
GO
reconfigure with override   
GO

Select allOpen in new window

Now you can execute the SELECT part as check if it works.

Since your vb6 application is transferring data from linked server to linked server*, I would test these two pieces of the puzzle separately: first ensure that SELECT selects, then that INSERT inserts, and only then combine them together - all that from within your vb6 application.

That said, i wonder, if you need to transfer the data from one Access database to another Access database, wouldn't it be easier to do it directly, without involving sql server?  you could use linked tables in Access mdb file, or you could do it programmatically (I guess, 10-15 lines of code, hardly more) in VB6/ADO or vb.net.

*) which is what I would do. Currently you have configured one mdb as linked server, but another you are trying to use by name. Why not to link it as linked server as well? The query would be

insert into server1...table1 select (...) from server2...table2

@Vadim
Tks your proposal but no tks. It is this way 1 file as link server for the clear reasons I have mentioned in my original post of the question if you read it carefully we have 2 software !!!! (1 developed by us VB6 and the second that is purchased in VB.net to which we can only communicate via SQL Server.

@Vitor
Tks testing reverting.

gowflow

@Vitor
Same error

gowflow

Something must be wrong. Can you post a screen shot of the command that you tried to run as well the message error?

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="C:\SURVEYTRACKING\Survey2014.mdb"')...Survey WHERE Survey.VESSEL = 'ABC - 058/14' and Survey.[Date Arrival] = '11/19/2014' ORDER BY Item

Same screen shot as in ID ID: 40689756

I remind you that we are changing settings in SQL Server but this instruction is connecting thru the file directly.
gowflow

Sorry, I wasn't refer to that code but the one to set the  'Ad Hoc Distributed Queries'. You said that you got error.

no the As Hoc Distributed Queries worked fine and now set to 1

Ok. Was hoping at least a different error.
Out of ideas now. Let see if during the weekend something popups on my head.

I think the problem in the SELECT statement failing is due to the Connection I have it as you proposed
.ConnectionString = "DSN=" & sBMBDatabase & ";Trusted_Connection=Yes;"

Where sBMBDatabase  = the linked server name created that is not conflicting with the name of the external file it is KSGBMB.

Now what I did is I rant the SQL Select statement posted before from within SQL Server and had to remove Survey. for Vessel and Date Arrival and it returned records correctly.

So I presume the problem we have lies in the connection string as when I try this one
.ConnectionString = "Provider=SQLNCLI11;Server=" & sServer & ";Database=" & sBMBDatabase & ";Trusted_Connection=yes;"

I get the following error

gowflow

> Tks your proposal but no tks

ok then. Post if you change your mind, for example if you decide to try other suggestions I made.

@Vadim

Since your vb6 application is transferring data from linked server to linked server*, I would test these two pieces of the puzzle separately: first ensure that SELECT selects, then that INSERT inserts, and only then combine them together - all that from within your vb6 application.

You are welcome to post connection strings and systax for both INSERT and SELECT.

That said, i wonder, if you need to transfer the data from one Access database to another Access database, wouldn't it be easier to do it directly, without involving sql server?  you could use linked tables in Access mdb file, or you could do it programmatically (I guess, 10-15 lines of code, hardly more) in VB6/ADO or vb.net.

I clearly replied why we need SQL server for the other software requirement that uses VB.net.

*) which is what I would do. Currently you have configured one mdb as linked server, but another you are trying to use by name. Why not to link it as linked server as well? The query would be

I would be happy to link both databases but as you noticed it is failing on 1 that is linked !!! so lets fix this one at least and then we will have all the time to link the second one and try.

Thank you
gowflow

>  as you noticed it is failing on 1 that is linked

No, I did not notice that. What you posted all had things like

SELECT * FROM `Survey` IN "C:\SURVEYTRACKING\Survey2014.mdb"

which is not a linked server. Link Survey2014.mdb in the same way as you linked  SurveyBMB.mdb as SURVEYBMB on your screenshot in comment 40690307. Once it's done, create new query in SSMS and run this:

select top 5 * from <linked-server-name>...<table-name>

i.e. if you linked Survey2014.mdb as SURVEY2014, run

select top 5 * from SURVEY2014...survey

Once you have it working in ssms, the next step is do it from vb6. Use the same connection string in vb6 as you do for any regular sql server query, such as:

Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=<sql-server-name>

Or you can add data environment and design it there. The command will be the same as in SSMS.

Let me know if it works. I actually just did all that and it all worked right away.

@Vadim
Thank you for your attempt.

Let me clarify. I have reasons not to link both files to SQL Server. So will have only 1 that is linked server and the second one need to be accessed via its physical address.

in your comment:

Once you have it working in ssms, the next step is do it from vb6. Use the same connection string in vb6 as you do for any regular sql server query, such as:

That is my main issue What is the correct connection string syntax !!!

I tried what you posted
Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=<sql-server-name>

and this is what worked
"Provider=SQLOLEDB;Integrated Security=SSPI;Server=" & sServer

I had to remove completely the Database name,
HOWEVER
connection is EXTREEMLY SLOW !!! running the SELECT * FROM KSGBMB...Survey
takes more than 2 minutes to return a recordset for 57 records !!! this is no way workable presume the provider is the problem or the other options ??? no clue !

gowflow

Let's clarify something. In your initial post, you posted this sql statement:

INSERT INTO SurveyBMB.Survey
SELECT * FROM `Survey` IN "C:\SURVEYTRACKING\Survey2014.mdb"
WHERE Survey.VESSEL = 'ABC 24/14' and Survey.[Date Arrival] = '11/7/2014'
ORDER BY Item

This sql syntax is valid for Access, but not for sql server. I guess, until now every expert here assumed that you are sending this query to sql server, but it looks like you are sending it to Access. Accordingly, the error you received was from Access driver rather than from SQL server.

Correct?

If it's correct, then how can you expect Access know what is SurveyBMB.Survey , if SurveyBMB is name of the linked server in SQL Server Access has no idea about?

Besides, this again looks too convoluted. If this sql runs in Access, and Access selects the records from external database Survey2014.mdb, and is supposed to insert them to another Access database SurveyBMB (which may be the one you are connected to, or yet another external one), why not to do it directly:

insert into survey in "c:\surveybmb.mdb" select * from survey "c:\server2014.mdb"

instead, you are trying to make Access to insert it into sql server where  c:\surveybmb.mdb is linked server.  So, before we proceed, please clarify all this. If you want to work with sql server and from there with Access databases as linked servers, then your vb6 program needs to connect to sql server and to send sql valid for sql server.

If your VB6 application connects to surveybmb.mdb, then all you have to do is

INSERT INTO Survey
SELECT * FROM `Survey` IN "C:\SURVEYTRACKING\Survey2014.mdb"
WHERE Survey.VESSEL = 'ABC 24/14' and Survey.[Date Arrival] = '11/7/2014'
ORDER BY Item

and forget about sql server.

Vadim

Tks for taking the time. let me clarify again:

I was using successfully
insert into survey in "c:\surveybmb.mdb" select * from survey "c:\server2014.mdb"

UP UNTIL the moment we have this new software that requires that the link database that is between htem and us namely surveybmb.mdb be in SQL Server as their software can only receive and send data thru SQL Server this is where all this came up !!!

So I know that if the file is form the outside not envolving SQL Server and thru Access I have no problem and been doing this for the past 30 years PLUS !

Now I have SQL Server to which I am a total newbie and I need to know the connection that can deal with SQL Server and Access at the same time link I originally posted

INSERT INTO SurveyBMB.Survey
 SELECT * FROM `Survey` IN "C:\SURVEYTRACKING\Survey2014.mdb"
 WHERE Survey.VESSEL = 'ABC 24/14' and Survey.[Date Arrival] = '11/7/2014'
 ORDER BY Item

Where:
SurveyBMB.Survey is a linked server in SQL Server
Survey2014.mdb is an Access database outside SQL Server.

gowflow

The sql statement you posted can't work because one part of it can run only on sql server (from SurveyBMB.Survey , SurveyBMB being linked server in sql server); while another part can run only in Access ( IN "C:\SURVEYTRACKING\Survey2014.mdb" ). Access does not know about linked server; sql server does not know syntax "IN <external database>", and in any case, one sql statement can't run in two different database engines at the same time.

You have to decide, whether your vb6 program will connect to sql server and run

insert into linkedserver1...table (...) select * from linkedserver2...table

or connect to Access and run

insert into table in "mdb1" select * from table in "mdb2"

(which is what you have been doing)

, or you can code in vb6 extraction into one recordset connected to Access, followed by insertion into another recordset connected to sql server; or it can be even one recordset changing its connection. But it can't be one sql statement.

Maybe I don't see the full picture, but I don't understand why you need to change anything at all. That fact that now surveybmb.mdb is also accessed by their application through sql server does not mean that you can't do anymore what you have been doing all the time, i.e "I was using successfully insert into survey in "c:\surveybmb.mdb" select * from survey "c:\server2014.mdb"

Maybe I don't see the full picture, but I don't understand why you need to change anything at all. That fact that now surveybmb.mdb is also accessed by their application through sql server does not mean that you can't do anymore what you have been doing all the time, i.e "I was using successfully insert into survey in "c:\surveybmb.mdb" select * from survey "c:\server2014.mdb"

Verry correct I am still doing this and the file is connected thru linked server so they can see it
HOWEVER
I am taking precautions as do not know how they will connect this file to SQL Server they mentioned something like thru Integration Services (totally ignore what it is) I am afraid that we will be brought to maybe need to communicate with SQL Server and not to Access as a linked Server this is why I am going with all this altogether.

Now this being said you mentioned 2 recordsets which is maybe a solution

Can you please provide the code and syntax from Connection to SQL to creation of recordset for
INSERT INTO SurveyBMB.Survey
SELECT * FROM `Survey` IN "C:\SURVEYTRACKING\Survey2014.mdb"
WHERE Survey.VESSEL = 'ABC 24/14' and Survey.[Date Arrival] = '11/7/2014'
ORDER BY Item

This way I would try it and if it is workable time whys and results then we would have maybe an alternate solution ?

Thank you for your detailed info as this is helping me greatly.

gowflow

Thank you for your detailed explanation on this subject which is much appreciated.

This is leaving me with a big question mark that I hope you are able to answer. It seems that the vendor is suggesting SSIS and probably are going toward this. So this being said my question is the following:
If they choose to use SSIS for attaching/Importing/Linking ... ??? our SurveyBMB.mdb database will they do it thru:
Import ?
Linking ?

In other words is it possible to use SSIS and have the database linked ? so we can still from our VB6 use the physical location of the database to read/write and they use SQL Server namely SSIS to read/write ?

This is the scenario that we need to achieve as both software need to look at this database in an online manner where the software of the vendor is used in premises outside the location of the office and update this database and we want our software to pickup these updates on an online status and update our software accordingly.

If SSIS means that they need to Import the data into SQL Server this means that they will need to Export the data as well each and every time the transaction are occurring which is not a solution. If it is linked then yes it would instantly reflect on both sides SQL Server and Physically.

Kindly clarify if possible.
Regards
gowflow

Hi gowflow,

SSIS is very good for Import/Export process and can be used as a solution for you. SSIS doesn't work with Linked Servers but Server Connections. It creates connections to all databases and then you can add objects to perform Imports, Exports, Transformations, run SQL scripts, run other type of scripts, etc. Also let you add .NET code so it's really very powerful. You can save the package and schedule it to run whenever you want.

> Kindly clarify if possible.

sorry, anything I'd clarify would be wild speculation, because we don't have the full picture of your data, i.e. what is in each Access database, what you do with them, by which applications, where the data is coming from and so forth. If your new vendor seems competent, I think your best bet would be to sit down with them and discuss the present and the future of your solution. In fact maybe it's good time to consider full rewrite of it, if it really was in place for 30 years. E-E question is good vehicle for isolated and specific technical problems, but in this case  what you want is solution architect - does not work too well in this "advice by mail" mode; as you can see, it took significant time to even realize what's going on in one trivial query. Besides the vendor, you will find link "hire me" in many e-e experts' profiles (including those who participated in this discussion), you can consider that as well.

Well thnk you for your info so far I am expert as well as you can see in my profile but not proficient at SQL Server.

All this being said, kindly advise if we can download (and how) SSIS if the version installed is SQL Server Express 2012 ? as it does not come bundled with it.
gowlfow

I suggest you start with evaluation:
http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2014

sql server 2014 developer edition:
http://www.amazon.com/Server-Developer-Edition-2014-English/dp/B00JKMY8KC
Not free, but close :-) - $14. I'm not 100% positive if it includes both license and media.

I see. Coz they had suggested we use SQL Server Express 2012 and now they are pointing to SSIS so presume will need to discuss internally to see best option as already they put us in a dead lock.

Meantime I noticed I have not commented on Vadim code for which I am sorry about it. Although for sure looping thru records is an obvious alternate solution but wanted to stick on an SQL type of Execute function.

Do you think we can pass a recordset as a parameter in an SQL ? so we benefit of the Execute ?
Let me clarify:

In your code:

Dim cn As New ADODB.Connection, rs As New Recordset, rs2 As New Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db.mdb"
rs.CursorLocation = adUseClient
rs.Open "select * from table1", cn
Set rs.ActiveConnection = Nothing
cn.Close
cn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Initial Catalog=db;Data Source=(local)"

Select allOpen in new window

and instead of now creating a new recordset
rs2.Open "select * from table1", cn, adOpenKeyset, adLockOptimistic

then instead build an SQL something like:
cn.Execute "INSERT INTO table1 rs" ???
where rs is passed as a parameter ??? not too strong in SQL but can we pass a recordset as a parameter to achieve a 1 time execution with Execute ??

Tks
gowlfow

No, you can't. Recordset is programmatic object in the client application. Client application sends to sql server Transact-SQL commands, that's the only thing SQL server understands.

> to achieve a 1 time execution with Execute ??

Unless you are working with millions of records, it's not something to worry about. The time it took for you to type your last comment most likely exceeded any time savings from such a solution you would achieve in 1,000 years of daily execution.

Pitty that you're using VB6. In .NET you already have a class for bulk copy data from another source to SQL Server.

pitty that your using VB6 ...

Well this software and also other are running the full company and this for several years now. I am sure there is newer and better but also what is the cost for migrating all this and time ... also need to take in consideration.

Anyway I will keep it at the code that Vadim posted and will accept as alternate solution.

I am in the mits of downloading SQL Server Advance package as understand it could include Integration Services.
have already downloaded SQL Data Tools  business Integration for Visual Studio 2012 that clearly showed Integration Services when downloading but after all done nothing was added to the Management Studio of SQL Server and seems the Integration Services would run in Visual Studio which is not what I am looking for.

Will revert once download complete of SQL Advanced and advise results.
gowflow

I never heard about SQL Server Advance package. What that should do?
As far as I know SSIS is installed from the SQL Server setup media.
Visual Studio has an extension for SSIS projects aka BIDS. But you'll always need a SSIS server for developing with BIDS.

> Pitty that you're using VB6

Vitor, I bet $50 that any reasonably trivial programmatic challenge (like this one) I will code in vb6 at least 5 times faster than in .net. Something like this I would code probably while visual studio would be still loading.

@Vadim: I don't bet but I believe on you ;)

So you both agree that VB6 is not a pitty ? thank you as I surely believe it is not ! as I had done extensive research when VS 2005 was out and needed to possibly migrate to the /net platform but saw the downside were far greater than the advantages so had droped this long time ago.

Advance package is
SQLEXPRADV_x86_ENU.exe
and is 1.2 GB !!! and looking into specific it seems it has possibility for SSIS.

Will revert
gowflow

Oh, do you mean SQL Server Express with Advanced Services. Unfortunaly the BI feature is only for Reporting: "•BI Development Studio. This provides an integrated report creation and design environment to create reports."

> So you both agree that VB6 is not a pity

The tool matters only as long as it's in right hands. Solutions are created by competent people, and what tool those competent people are using is irrelevant. Usually every pro has his own tool of choice he can do pretty much anything with, and does not care whether it's fancy or not. So the claims that newer tool will automatically bring higher productivity are nothing more than sales literature.

Besides,  the determined Real Programmer can write FORTRAN programs in any language.

but can we pass a recordset as a parameter to achieve a 1 time execution with Execute ??
You can pass Table Valued Parameters (which is the same as what you are asking) to a Stored Procedure so that you can insert all the rows at once, unfortunately I don't believe you have that option with VB6.  It is ADO.NET only.

Although all of this discussion did not solve my problem, I got some knowledge on SSIS (however brief) but one think for ure is the conclusion that SSIS cannot work with SQL Server Express 2012 and need a full license to do so.
Tks for all the effort and patience on these threads.
gowflow