CODEWITHSUNDEEP
sql-serverms-accessodbc
Osify
Osify

Reputation: 2295

Import from MS SQL Server to MS Access via ODBC lost all indexes

I use SQL Server 2014 / 2017 (either expression version), I tried to move data from the SQL Server database to MS Access for some reasons, but what I face now, all indexes that created on each field in each table in SQL Server are not transferred to MS Access (2016).

Currently, the workaround is: I have to go to the tables that I need to set flag: index as example:

enter image description here

Is there any solution that can keep all indexes from SQL Server to Access?

Upvotes: 2

Views: 386

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49039

You can at least get the PK and index(s) to come down from SQL server.

There are two ways to do this.

From the GUI?

Simply link to the table in question (create a linked table to sql server).

Then in the nav pane, simply right click on the linked table and choose “convert to local table”.

The above will thus preserve the PK (and indexes). And for any other column that has an index, you find Access also preserves (creates) the index local for you also.

If you are using code?

Then, first create the linked table.

Then execute the covert to local table.

Eg this code:

Dim strCon        As String
strCon = CurrentDb.TableDefs("dbo_TimeTest1").Connect

DoCmd.TransferDatabase acLink, "ODBC Database", strCon, acTable, _
           "dbo.tblbooking", "tblBookingLocal2", False, True
DoCmd.SelectObject acTable, "tblBookingLocal2", True
DoCmd.RunCommand acCmdConvertLinkedTableToLocal

In above, I just grabbed a “known” and existing connection string from another linked table to the same SQL database.

Upvotes: 2

Related Questions