SELECT data records from MS ACCESS database using MSSQL Server

Question

I already successfully created a link from the other workstation and I can view all the tables from MS ACCESS database. But I can't retrieve the data from the tables using simple SELECT Statement.

SELECT * FROM [192.168.1.64].default.dbo.CHECKINOUT;

When I execute this query i got this error:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'default'.

I also found this query:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=192.168.1.64;Trusted_Connection=yes;',
     'SELECT *
      FROM default.dbo.CHECKINOUT') AS a;
GO

but got this error:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 1231, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [1231].

Default is a keyword. Try [192.168.1.64].[default].dbo.CHECKINOUT — Igor Borisenko
– Igor Borisenko, Commented Dec 22, 2015 at 7:29
@Igor - i got this error with your code: Msg 7312, Level 16, State 1, Line 1 Invalid use of schema or catalog for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "192.168.1.64". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema. — Lekz Flores
– Lekz Flores, Commented Dec 22, 2015 at 7:32
I'm not following what's going on here. SQLNCLI and SQLNCLI10 are providers for connecting to other SQL Server instances. Normally for MS Access, you need to use the Jet 4.0 provider (Microsoft.Jet.OLEDB.4.0) or the ACE provider (Microsoft.ACE.OLEDB.12.0). See example here. Note that the provider takes a file path, not a network location. How exactly is this Access database set up? Does the workstation have another instance of SQL Server installed? — Bacon Bits
– Bacon Bits, Commented Dec 22, 2015 at 7:34
@BaconBits, the other workstation don't have SQL Server installed. And also I'm trying to access other workstation meaning the filepath would look like: \\192.168.1.64\C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb ? — Lekz Flores
– Lekz Flores, Commented Dec 22, 2015 at 8:26

Gord Thompson · Accepted Answer · 2015-12-24 01:52:58Z

1

When creating a Linked Server in SQL Server that points to an Access database we refer to a specific database file, so "catalog" and "schema" have no real meaning in that context. Therefore, when specifying an Access table as a four-part name in T-SQL we simply omit the second and third parts:

SELECT * FROM [AccessLinkedServerName]...[TableName]

or, in your case

SELECT * FROM [192.168.1.64]...[CHECKINOUT]

answered Dec 24, 2015 at 1:52

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

Collectives™ on Stack Overflow

SELECT data records from MS ACCESS database using MSSQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related