Access 2007 to sql 2005

[access_developer]

Hello Friends,

I am using two database's of access as front end and back end
Front end is used as application and back end is used for data storage.

I have a problem with access back end database due to the size constraint and i need to make this back end as SQL database

I have to migrate all the data from database2(back end) of access to sql server 2005

I try to do the upsizing but i am getting errors on few tables as i have no primary key on the table and the error is " Attached table is not updatable because it has no unique index"

i have to make sure all the relationship, queries etc to function the same way as it should in access database

I would be thankfull if you can suggest me how to migrate from access 2007 to sql 2005 server.

any replies will be appreicated.

Thanks in advance

 

[pbaldy]

As the error implies, for SQL Server tables to be editable in Access they must have a primary key. You can either do it in Access prior to upsizing, or do it in SQL Server and relink the tables.

 

[access_developer]

Thanks for the prompt reply

Actually i am newbie to this migration and have no knowledge what so ever going forward.

I will create the primary keys in access before upsizing but i have little question about migration

The queries in access database are not getting migrated so what is the possible workaround on that ??

and i have one more question

usually i have update process which takes place in access front end and it pulls the text files and used to update the backend access database now

Now my concern is after i link the tables ? how do i make sure the data is getting updated to the SQL tables and what i need to change in VBa Code or access queries ( Front end ) to achieve update process
I mean do i have to change the sql drivers in vba code/access queries front end.

any replies will be appreciated

Thanks in advance.

 

[pbaldy]

I haven't used the upsizing wizard in 2007; are you upsizing the tables or creating an ADP (Access Data Project). ADP's sort of went out of favor, so they're not supported as well as previously. Personally I didn't use them, and I'm not even sure 2007 still lets you create one. If you're just upsizing the tables (which is what I would do), then queries wouldn't be migrated. What you should end up with is linked tables instead of local tables. Most if not all of your processes and queries should work with the linked tables just as they did with the local tables.

 

[access_developer]

Thanks for the quick response

I am wondering if i link the sql tables to database 2 ( existing acess back End) then how about database 1 ( existing front end application)

so there will be 3 databases

1. access front end app
2. access back end app
3. sql link to access back end

I am confused and wondering how it will work

any thoughts will be appreciated. Thank you

 

[pbaldy]

Typically there is no Access back end. The front end contains links to the SQL Server tables. If for some reason you still needed to have some data in an Access back end, the front end would link to them, but that Access back end would not contain any links to the SQL Server tables. In other words, links from Access front end to SQL Server, links from Access front end to Access back end, but no connection between Access back end and SQL Server.

 

[access_developer]

Thanks for your response.

what i can do here is to copy all the queries from the backend and paste the same queries in the front end access app and then i would create SQL link tables to the front end access application.

But what i am wondering what else need to be taken care of apart from link tables and queries ( will import from existing back end database)

Do i have to be make any changes apart from linking tables and queries ?

Do we have any other techinques or tools which i can use for migrating tables and relationships from access back end to the sql

I heard only two upsizing / SSMA which one is better for migrating or do we have any other tools i can make use of

Thanks once again

 

[DCrake]

I would imagine that the tables that do not contain primary keys are not data intensive, such as lookup tables, etc. If that is the case simply migrate the tables that are data intensive to SQL and keep your other tables in Access. Then link the SQL tables via ODBC. There is nothing stopping you from having different data sources in your front end.

 

[pbaldy]

There's nothing stopping you, but in my experience it can slow things down when you join SQL Server and local tables, if the SQL Server tables are large. JET won't be able to let SQL Server process the query because of the joined non-SQL Server table, so it will have to pull the whole table over the wire to process it.

 

[access_developer]

Thanks once again for your help and this is very helpful to a newbie like me.

So shall i make use of ODBC ??

Do i have to be make any changes apart from linking tables and queries ?

Do we have any other techinques or tools which i can use for migrating tables and relationships from access back end to the sql

I heard only two upsizing / SSMA which one is better for migrating or do we have any other tools i can make use of

Thanks once again

 

[boblarson]

I heard only two upsizing / SSMA which one is better for migrating

I would say SSMA is better than using the internal Access upsizing.

 

[access_developer]

Thanks for the reply

I did the migration using SSMA tool and was good but few errors in relationships and tables.

But the problem i have rite now is all the tables are added with dbo_table1 etc

and my front end application just have table1 and when i try to rename the tables in sql its not allowing me to do because of the relationships and schemas

after linking the tables from SQL to access how should i update the tables or rename the tables or can i rename the tables in front end access application itself which i highly doubt as you have no control to make changes to design view tables.

the database is acting wierd with the speed as well

any workarounds on how to rename the tables in sql or access front end application to the link SQL tables ?? will higly appriecate the thoughts

Thank you

 

[boblarson]

you can rename them in the Access frontend. Just go to the tables in the frontend, rightclick and select rename.

As for the speed, you will want to look at this performance FAQ.

 

[access_developer]

Thanks for the reply, its really Helpful

If i rename the tables in access front end then will i have any issues updating or adding or deleting any records from front end app ?

Thank you

 

[boblarson]

Thanks for the reply, its really Helpful

If i rename the tables in access front end then will i have any issues updating or adding or deleting any records from front end app ?

Thank you

you should not have any issues. When you rename the linked tables in Access the objects within Access use that alias but the link still uses the right name to the backend so it communicates.

 

[access_developer]

Thanks Bob

I was able to rename the tables from access front end.

You rock thanks