I've been handed an Access Database to assess the possibility of moving to SQL and to put a new front end on to it.
I've opened the database. My exposure/experience to access is limited.
The first thing I've done is to click the relationship button and add all the tables, there are no relationships defined.
Is it possible that these could be done in the code? (I've had a quick look, doesn't appear to be) If so would it be exposed by the relationships view?
Thanks in advance.
Have you looked at the "SSMA" tool from Microsoft? It is a reasonably good "SQL Server Migration Assistant". On simple databases its a point and click and you are done. On more complex systems its at minimum a great first step.
I have migrated several major customized access database/applications used by several local municipalities with success.
One word of caution: Watch out for the "Denial of Service" attack that Access will do on your SQL server. The default login for Access is a user named "Admin". If you use windows authentication against SQL, it will attempt to authenticate first as "Admin", then using the users credentials. The combined number of users running your application can form an effective denial of service attack (even though it is unintended). The only method I found around this was to not use windows authentication against SQL.
@Kryptos: It is more likely that there are no relationships involved in the design at all.
Relationships have nothing to do with the sheer number of connections to a database.
I am familiar with the basics of database design. I know when indexes are required, how to tune queries and such. If you are relying upon the "relationship" tool within access to optimize your database you are missing a lot of performance.
If you are going to be migrating your application into a pure SQL database with an Access front end then get completely out of Access (data wise) and forget the toys.
There may be implicit relationships coded into the software without any relationships defined in the database schema, I have found that to be common in Access databases in the past (thankfully I no longer encounter them).
I'm with lensman; copy the schema and data into SQL Server and then set up indices and foreign key constraints.
Thanks for your reply's. I was ill yesterday.
OK, I look into it. Thanks.
It is more likely that there are no relationships involved in the design at all.
This. Lots and lots of the time the table creators were apparently thinking 'flat file.' This probably has a lot to do with access being the entry point for talented amateurs to get into programming, ie
1. a company needs something done,
2. looks around the staff for someone with some ability,
3. said someone develops an app that works,
4. the app starts to be used,
5. now the company's stuck and can't get out of it
Comments have been closed since this content was published more than 30 days ago, but if you'd like to continue the conversation, please create a new thread in our Forums, or Contact Us and let us know.