[Resolved] Compare Access and SQL Server

[mel_flynn]

Hi there,

I always use Access databases as my data store when developing - usually just small apps with a few users. However, I now, potentially, have a customer who may need SQL Server. They are not yet sure of their requirements, number of records, number of users etc. However, it may go up to thousands of records and tens of users. Basically their data is all currently stored in Excel Spreadsheets and they need a database. I am quite an expert on Access but I have never used SQL Server.

My question is:
If I begin developing their database in Access (they have Access installed on all their PCs at the mo), can I easily convert/upload the data to SQL Server if required?

Also, if I do change over to SQL Server, could I still use Access to run queries & reports?

Regards,

[amrita]

Access MDB file will connect to SQL server and become .ADP file.Go through the Tools->Database Utilities->Upsizing wizard. Then in the code DAO to ADO conversion is required.

[mel_flynn]

Thanks. Would it be advisable to continue to use the queries and reports in Access or is there similar, user-friendly tools in SQL Server?

[Hack]

My question is:
If I begin developing their database in Access (they have Access installed on all their PCs at the mo), can I easily convert/upload the data to SQL Server if required?

Look into DTS (Data Transformation Services) packages in Enterprise Manager. You can easily import an Access Database into SQL Server.

[amrita]

After creating the ADP project you cannot use the queries of access .You have to the SQL equivalent Views or Stored procedures.

U can very well use Access Reports but change the DAO code ADO to connect to SQL database

[mel_flynn]

Thanks