migrating access database to sql server (1 Viewer)

[giovi2002]

I would like to migrate an access application to sql server.
I have much experience with adp but I don't want to rebuild the application.

Would it be possible to use ODBC and use sql server tables over ODBC without needing to rebuild the queries in SQL Server?
So I want to leave the queries in access and let sql server handle the queries to get a client server solution. I have many access parameters (like forms![control]..etc) in my queries so I hope this will work

 

[SQL_Hell]

I would like to migrate an access application to sql server.
I have much experience with adp but I don't want to rebuild the application.

Would it be possible to use ODBC and use sql server tables over ODBC without needing to rebuild the queries in SQL Server?
So I want to leave the queries in access and let sql server handle the queries to get a client server solution. I have many access parameters (like forms![control]..etc) in my queries so I hope this will work

You are going to have to update the queries to SQL server, whats the point of having sql server if you're not going to take advantage of its features. If you leave the queries in access then they will run on the client not the sql server.

None of your form references are going to work either these need to be updated to be sql server compatable.

Use the upsizing wizrd in access this will convert most of your queries for you

 

[shadow9449]

Use the upsizing wizrd in access this will convert most of your queries for you

The wizard will actually convert the queries to stored procedures? How does the server end know what criteria to send if it's based on user input on the form (which is on the client end)? Sounds pretty cool to me if it works

SHADOW

 

[pbaldy]

You are going to have to update the queries to SQL server, whats the point of having sql server if you're not going to take advantage of its features. If you leave the queries in access then they will run on the client not the sql server.

You don't HAVE to move the queries to SQL server. They may run faster as stored procedures, but they will still run as Access queries. My understanding of the "inner workings" is that Access will have SQL Server process the query if it can, so in many/most instances the query will be processed by the server rather than the client, and only results will be sent over the network. The exception would be instances where SQL Server doesn't understand the SQL, for instance if IIf() or Format() is used, as these aren't T-SQL compliant.

 

[Kodo]

if you make an Access Project out the app, then by default , all "queries" you create are stored procedures. You don't have a choice.

 

[pbaldy]

Kodo, if that was directed at me, I'm aware that what you're saying is true. My impression is that the OP was asking about leaving the app as an MDB. They specified that they didn't want to rebuild the app.

 

[SQL_Hell]

You don't HAVE to move the queries to SQL server.

Yes ok you don't have to, but personally I think its advisable.

Shadow,

When you run the upsizing wizard as Kodo rightly says you will get a .adp (front end) and sql server database (backend). The .adp will contain forms and reports.
Your queries that are only select statements will be converted into SQL server views and the other queries such as update, insert etc will be converted into stored procedures. The queries that contain form references will be converted into stored procedures but they will not work straight away you will have to set up some input parameters in the .adp. When you get to this stage let me know and I will talk you through it.

Hope this makes things a bit clearer, good luck

 

[shadow9449]

[Your queries that are only select statements will be converted into SQL server views and the other queries such as update, insert etc will be converted into stored procedures. The queries that contain form references will be converted into stored procedures but they will not work straight away you will have to set up some input parameters in the .adp. When you get to this stage let me know and I will talk you through it.

Hope this makes things a bit clearer, good luck

Wow...fabulous.

If you've created client/server applications by upsizing Access applications to SQL Server in the past, I have a whole stack of questions for you. I've spent the past month reading books on SQL Server hoping to learn to be able to do this, and I would love to ask someone with experience in the area some of my questions.

Great to have a resource like this

SHADOW

 

[giovi2002]

Thanks for the replies.
I downloaded sql server express as it's a project which is not at my work.

I have mucht ADE experience and that's why I didn't want to rebuild all the queries which have form references.

I will try ODBC or linked tables and will check at the server side or everything is processed at the server.
Will keep you up to date