VBA - MS ACCESS to external SQL Server

Question

I have a problem with this instruction in VBA that tries to import data from an external SQL Server DB into a local MS Access DB:

Conectar = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\VENTAS\VENTAS.mdb;" _
            & "Persist Security Info=False;"

cn.ConnectionString = Conectar    
cn.Open

SQL= "insert into PRO (COD_PRO,DESC_PRO) " & _
     "select COD_ARTICU,DESCRIPCIO  " & _
     "FROM OPENDATASOURCE ('SQLNCLI', 'Data Source=VAIO\SQLEXPRESS;User" & _ 
     "ID=XXX;Password=XXX;').ACPE.dbo.STA11 where perfil<>'N';"

cn.Execute Sql

The error

"Syntax error in FROM clases"

It seems that I can't use OPENDATASOUCE to an SQL Server from Access?

Parfait · Accepted Answer · 2016-11-13 17:03:17Z

You need to reverse the query reference. OPENDATASOURCE is a SQL Server TSQL command and is not an MS Access SQL command. Consider running an ODBC inline of a stored query:

SQL (save as stored query or script as VBA string)

INSERT INTO PRO (COD_PRO, DESC_PRO) 
SELECT COD_ARTICU, DESCRIPCIO
FROM [ODBC;DRIVER={SQL Server};server=VAIO\SQLEXPRESS;database=ACPE;UID=XX;PWD=XXXX].STA11;

VBA

DoCmd.OpenQuery "queryName"
' OR CurrentDb.Execute strSQL

Alternatively, create a linked table, then run the append query as linked tables can interact with local tables:

DoCmd.TransferDatabase acLink, "ODBC Database", _
      "ODBC;DRIVER={SQL Server};server=VAIO\SQLEXPRESS;database=ACPE;UID=XX;PWD=XXXX;", _
       acTable, "STA11", "STA11"

strSQL = "INSERT INTO PRO (COD_PRO, DESC_PRO) 
          SELECT COD_ARTICU, DESCRIPCIO
          FROM STA11;"

CurrentDb.Execute strSQL

M.Hassan · Accepted Answer · 2016-11-13 16:50:06Z

0

OPENDATASOURCE is T-Sql statement and is used from within SQL Server not from Access, so you get this error.

To insert data from PRO table which is located in sql server you can:

Create a linked table in VENTAS.mdb to Sql server table "PRO"

Or

create pass through query to Sql Server Pro table in VENTAS.mdb.

answered Nov 13, 2016 at 16:50

M.Hassan

11.2k5 gold badges74 silver badges96 bronze badges

7 Comments

user20416 Over a year ago

Would a pass-through query recognize the tables in the local Access database?

M.Hassan Over a year ago

@Zev, yes, pass-through query can access any ODBC connection, including odbc to acess database. In the OP you create pass through query in VENTAS.mdb to SQL server.

user20416 Over a year ago

Would a pass-through query recognize the tables in the local Access database at the same time as it is querying tables in the remote SQL Server?

M.Hassan Over a year ago

@zev, Have a look to: support.microsoft.com/en-us/kb/304323 , for pass-through query to sql server.

Parfait Over a year ago

@ZevSpitz - No, pass-through queries only run in the scope of connection and cannot see the local database from which it was called. You can make-table from it: SELECT * INTO [localtable] FROM [passthruQ]

|

Collectives™ on Stack Overflow

VBA - MS ACCESS to external SQL Server

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related