IN SQL Server 2014 how to select access mdb table ? pls sample!

Question

IN SQL Server 2014 how to select access mdb table ? pls sample!

Answer 1

Why have you tagged your post with Win 10; there is no relation to? See MS Learn => OPENROWSET (Transact-SQL) => Examples => B. Using the Microsoft OLE DB Provider for Jet

Answer 2

Hi,

I'd be happy to help you out with your question. Sorry for the inconvenience caused.

To select data from a Microsoft Access database table in SQL Server 2014, you can use the OPENROWSET function. The OPENROWSET function allows you to access data from a remote data source, in this case, your Access database file.

Here's an example query you can use:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
                        'Data Source=C:\path\to\your\access\database.accdb;
                         User ID=admin;Password=yourpassword',
                        TableName)

To use this query, you'll need to replace C:\path\to\your\access\database.accdb with the actual path to your Access database file, yourpassword with the actual password (if any), and TableName with the name of the table you want to select data from.

It's important to note that you may need to install the Microsoft Access Database Engine (either 32-bit or 64-bit depending on your SQL Server architecture) to be able to use the Microsoft.ACE.OLEDB.12.0 provider.

For more Information, please refer to following resource :-

SELECT statement (Microsoft Access SQL) - https://learn.microsoft.com/office/client-developer/access/desktop-database-reference/select-statement-microsoft-access-sql

If you have any other questions or need assistance with anything, please don't hesitate to let me know. I'm here to help.
 
If the reply was helpful, please don’t forget to upvote or accept as answer, thank you.