Forum Discussion
Migration Access DB to Latest Version of Access DB dbo. vs dbo_
Hi All,
I am migrating an older version of access db to a latest version. My database is split into two frontend and backend. The backend is stored in a 2000 SQL Server. While doing so the frontend .mdb is getting converted smoothly but not able to convert the Backend SQL Server DB. Questions are:
a. How do I connect the SQL Server DB (backend) into new access db instance.
b. The Linked Table Manager doesn't show anything. Not even the System or User Data Sources
Names
c. All table names are converted into dbo_ from dbo.
Highly appreciate your help in this usecase.
Regards,
SB
A. You can't "convert" the back end from SQL Server 2000 to something else from Access. You'll need to install whatever newer version you choose, and then migrate the data to it.
B. Same steps as now, except the new tables will be in the new database with a new connection.
C. Unlike Access, SQL Server has "schemas" which are like separate buckets, or containers for tables. DBO is the default schema. We all get used to seeing it, but you can create other schemas in SQL Server and segregate tables among them if you wish. Because Access doesn't follow the schema.table method, it changes the dot . to an underscore _ and uses schema_table for the default name in Access. No big deal. You can change the names in Access if you want to retain the original names.
Given the possibility that I misinterpreted your reference to not being able "to convert the back end SQL Server DB", here's a second option.
Assuming that what you meant was that you want to link the newly created accdb to the same SQL Server 2000 database, you can do that by using the External Data tab on the ribbon to locate and link to it.
That said, SQL Server 2000 is long out of support. You might want to consider upgrading it to a more recent version as well. SQL Server Express is free. For most Access-level applications is it perfectly adequate.
