Trying to Import data into SQL Server from Access

Question

I tried this small script below.

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[Import_From_Access]    Script Date: 4/16/2017 5:13:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Import_From_Access]
AS
BEGIN


SET NOCOUNT ON;

EXEC sp_addlinkedserver
@server = 'EXCEL-PC\SQLEXPRESS',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:\Users\Excel\Desktop\Coding\Microsoft Access\Northwind.mdb'

EXEC sp_addlinkedsrvlogin 'EXCEL-PC\SQLEXPRESS', FALSE, Null, Admin, Null

END

Then, to run it, i tried this:

SELECT *
FROM OPENQUERY('EXCEL-PC\SQLEXPRESS', 'SELECT * FROM Table1')

Now, I thought that would work, but I'm getting an error message that reads: Msg 102, Level 15, State 1, Line 6 Incorrect syntax near 'EXCEL-PC\SQLEXPRESS'.

I am thinking that the issue may be the '-' character or the '\' character. unfortunately, that is the name of the server machine, and it's probably not going to change. If there a work-around for this kind of thing, or am I just out of luck with this kind of setup?

Thanks to all!

Mikhail Lobanov · Accepted Answer · 2017-04-17 08:51:09Z

1

EXCEL-PC\SQLEXPRESS isn't a string literal:

Try this:

SELECT *
FROM OPENQUERY([EXCEL-PC\SQLEXPRESS], 'SELECT * FROM Table1')

answered Apr 17, 2017 at 8:51

Mikhail Lobanov

3,02612 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ASH Over a year ago

Oh my, I am so dumb sometimes. Yes, yes, yes, that basically worked. I just ran this: SELECT * FROM OPENQUERY([EXCEL-PC\SQLEXPRESS], 'SELECT * FROM Table1') Now, I get this message: Msg 7411, Level 16, State 1, Line 5 Server 'EXCEL-PC\SQLEXPRESS' is not configured for DATA ACCESS.

ASH Over a year ago

Actually, I think it is happening because Access is 32-bit and SQL Server is 64-bit. I think that is the issue. The link below should help to resolve the issue. microsoft.com/en-us/download/…

Collectives™ on Stack Overflow

Trying to Import data into SQL Server from Access

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related