Solved Migrating tables from Access 2019 to SQL 2019 - help... (2 Viewers)

[Garren.Shannon]

Hey all,

Again, thanks to all who reply. This forum is great for nubies like me. I only have a tacit understanding of SQL server operations and I do have a working knowledge of SQL commands.

I most likely didn't do this correctly but I do have a backup. I have a fairly simply Access DB I crafted to track school bus ridership logs for reporting and getting funding from the state. I had trouble using the SQL Server Migration Assistant for Access tool so I tried going the "Split Database" with the "SQL 2019 Import and Export Data" tools. I now have tables in SQL but the original DB now looks for the tables in the link file... be.accdb. I used the be.accdb file to import the tables into SQL.

How do I tell the program to look for each table in our SQL server rather than the local link file? Or am I going down the complete wrong path here?

 

[theDBguy]

I think maybe the simplest approach is to delete all linked tables and go through the External Data tab again to link the new SQL Server tables.

 

[GPGeorge]

Hey all,

Again, thanks to all who reply. This forum is great for nubies like me. I only have a tacit understanding of SQL server operations and I do have a working knowledge of SQL commands.

I most likely didn't do this correctly but I do have a backup. I have a fairly simply Access DB I crafted to track school bus ridership logs for reporting and getting funding from the state. I had trouble using the SQL Server Migration Assistant for Access tool so I tried going the "Split Database" with the "SQL 2019 Import and Export Data" tools. I now have tables in SQL but the original DB now looks for the tables in the link file... be.accdb. I used the be.accdb file to import the tables into SQL.

How do I tell the program to look for each table in our SQL server rather than the local link file? Or am I going down the complete wrong path here?

Your Access FE wasn't altered when you migrated the tables to SQL Server using the Accdb BE, so the solution is, as theDBGuy says, to relink the Access FE to the SQL Server tables.

 

[Garren.Shannon]

Is that done in the be.accdb file or from my original? And I can't find where the links are to delete or relink. I see the Linked Table Manager but only offers options to export to Excel.

 

[theDBguy]

Is that done in the be.accdb file or from my original? And I can't find where the links are to delete or relink. I see the Linked Table Manager but only offers options to export to Excel.

Open your FE file. If you can see the Navigation Pane, you can then delete all the linked tables there.

 

[Garren.Shannon]

OK... I think I am finally getting it. I have 5 tables. I imported them into SQL. In the FE (Front-End... spent a few min's looking for an actual file with and FE ext.) DB, I delete one of the tables, then add a new link to the table in SQL. Totally makes sense. I created the link to Drivers. The table then showed as dbo_Drivers. I renamed the table to simply drivers matching my original linked name and no errors.

You guys are BRILLIANT!!!

Follow on question... the wizard noted importing queries. Should I likewise import the queries into my SQL server?

 

[Isaac]

I tend to link all the sql tables, and then go through the tables 1 by 1 so as not to lose track........first I rename the access table OriginalTableName_Old, then I re-name the linked sql object OriginalTableName

done

 

[Garren.Shannon]

Thanks for the pro-tip Isaac. What about queries? I noted when I tried to use the SSMA tool that it listed queries as an import item. Should I be trying to import queries into SQL?

 

[Isaac]

I guess I'm not familiar with that. Technically there is no object in SQL Server called Queries (unless I have missed a lot over the years), I wonder if by saying that, the dialogue box you're referring to means Views.

But as a question, that's highly personal to your own opinion and purposes for different objects. You can import Views into Access if you think you have a good useful fast-working View in sql server, sure!

Frankly I use as few Views in SQL as possible - which is not to say I don't have to use them regularly, just as few as possible.
They tend to be an addiction that builds upon itself in an ugly way - and it's kinda hard to understand that perspective until you've built a few views-upon-views-upon-views. Once you start nesting views AT ALL, I usually prefer to stage nightly data via ETL instead, but now i'm getting side tracked. SQL server is great, I enjoy conversating about it. I have no Access work at my current job but enjoy all the pieces how they can work together.

 

[Minty]

Queries can probably stay in Access, unless they are very complicated, in which case they may well run quicker in SQL server.
Generally, you would use a view to provide a simplified dataset where there are many connected tables and use stored procedures to do complex data manipulation on the server.

You should avoid connecting local tables to SQL tables in queries, and avoid calling customised VBA functions within Access Queries connected to SQL tables as the ODBC Driver can't translate them into SQL Functions, meaning all the data gets dragged into Access for processing.

TBH there are many many things you can do once Access data is moved into SQL but takes things slowly, and read up on the possibilities.

 

[Isaac]

OH - were we talking about going from Access to SQL ?? If so sorry, I was thinking the opposite!
I thought linking objects from SQL into Access.

Yeah - the 'queries' export option just exports the RESULTS of a query to SQL Server and has nothing to do with translating your Access SQL Query into a SQL Server query. Which probably no tool can do, but even if it could, you wouldn't want that - you'd want to go through it and make the judgment calls yourself as there is no 'one answer' to how to translate a given phrase or command from jet-sql to t-sql.

 

[Garren.Shannon]

Queries can probably stay in Access, unless they are very complicated, in which case they may well run quicker in SQL server.
Generally, you would use a view to provide a simplified dataset where there are many connected tables and use stored procedures to do complex data manipulation on the server.

You should avoid connecting local tables to SQL tables in queries, and avoid calling customised VBA functions within Access Queries connected to SQL tables as the ODBC Driver can't translate them into SQL Functions, meaning all the data gets dragged into Access for processing.

TBH there are many many things you can do once Access data is moved into SQL but takes things slowly, and read up on the possibilities.

Thanks Minty... that is great advice. The steps I took were to split the database, Import the 5 tables from the BE file into SQL then in the FE file, deleted the 5 tables and created/added links to the SQL table. I then renamed each to the original name and everything works. So I think I, by happenstance, followed your suggestion... and I very much appreciate your feedback.

 

[Garren.Shannon]

OH - were we talking about going from Access to SQL ?? If so sorry, I was thinking the opposite!
I thought linking objects from SQL into Access.

Yeah - the 'queries' export option just exports the RESULTS of a query to SQL Server and has nothing to do with translating your Access SQL Query into a SQL Server query. Which probably no tool can do, but even if it could, you wouldn't want that - you'd want to go through it and make the judgment calls yourself as there is no 'one answer' to how to translate a given phrase or command from jet-sql to t-sql.

I didn't even know you were thinking reverse. So no problem at all.

 

[Pat Hartman]

Now that you're done you don't need this but someone else might. This is a sample database that has some useful code. One of the procedures renames the linked tables to remove the "dbo_" or any prefix for that matter from the tables that you link directly from some ODBC source. Of course, if you are going to try one of the included procedures, BACK UP your database first. You don't want to run update queries you've never tried before without testing first

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[Garren.Shannon]

OK... so I ran into not-sure-what issue. When testing adding a new record, the action complains that my primary key will not accept nulls. I am guessing it's because the metadata of setting the key field to auto-increment did not transfer. How does one now go into SQL and tell it to auto-increment the primary fields?

 

[Pat Hartman]

Did you open the linked table in design view? You can see there if Access thinks the PK is an autonumber. You can also see all the indexes access "sees". If Access doesn't see the autonumber, you need to either fix the problem in the original BE and do the conversion again OR open SSMS and fix it there.

Keep in mind that SQL Server and the Access FE aren't linked at the hip the way Access and ACE are linked. Therefore, whenever you make a change to the design of a table in SSMS, you MUST relink the tables for Access to "see" the change.

 

[Minty]

In addition to Pat's advice, you may need to set the fields "Identity" property in SSMS to yes, this is the equivalent of the Autonumber in SQL server:

This is probably quicker where there are only five tables than redoing the whole import process.

 

[Garren.Shannon]

Hey all,

So my next hurdle... after migrating the tables to SQL, I note that the primary keys no longer auto-increment. Of course, in Access, one sets the field to auto-increment. When running the DB now, I get "cannot insert null..." which I am sure reflects the lack of auto numbering. I don't see a property in the SQL to set auto-number. Is this accomplished via code now?

 

[Minty]

You need to set the field as Identity = Yes as per my previous post.
It should pick up your existing numbering and start the next new record from the next available number in the sequence.

Then right-click the table(s) in Access and select the refresh link option, to pick up the design change.

 

[Garren.Shannon]

Got it... Thanks Minty... but I am getting the following when trying to modify the tables.