Upsizing to SQL - best practice re VIEWS and SP

[liddlem]

Hi All
I'm wanting to upsize my database to a SQL server - still using Access as the front end.

Do I need to convert ALL events (and code in my modules) to Stored Procedures on the SQL server?
How would I refer to fields/values on forms (or other queries)

I also get the impression that I should convert my standard SELECT queries from the access front-end to VIEWS on the server.
If I do this, how to I get the views to reference a field value that is on my access form?

Is there a tool that will convert my existing code to SPs and views?

 

[pbaldy]

Personally I start with linked tables and native Access queries. I move things to stored procedures, views, etc based on need (poor performance). SQL Server provides a tool, SSIS I think, but I haven't used it.

 

[isladogs]

My experience is similar to Paul's except that I do use SSMS to manage the SQL backend of several large Access databases ( in one case with over 300 linked SQL tables)

I've been using SSMS for about 8 years and the free SQL Server Express version is still more than adequate for my needs

The stability of SQL server is of course the main advantage
In addition, I can easily write scripts to roll out changes in table design to clients.
Much easier than doing this for an Access backend db

SSMS does have built in assistance for creating stored procedures but I'm no expert in using these

Both views & stored procedures are useful to speed up processing time in certain complex cases.
However, in general I stick to doing queries in Access unless the time needed is far too long.

I've never needed to refer to Access forms in SSMS so can't answer your question.
If you find out, I'd like to know

Creating views in SSMS are in many ways similar to designing queries using the graphical interface in Access.

 

[liddlem]

Thanks for your input.
I use SSMS to create and maintain tables on the BE, but have no idea how to build a FE solution with either SSMS or Visual Studio. Hence ....Access.

Am I right to assume that you're saying (for you) it's more about improving response time (whether the problem is related to a dodgy network or server processing time) than about best practice per se' ?

My thinking is that it would be easier (and FAR more secure) to maintain the code on the server side, than having it in the FE. Or is it a case of not being able/practical to have ALL the code on the BE for some reason?

 

[isladogs]

I use SSMS to create and maintain tables on the BE, but have no idea how to build a FE solution with either SSMS or Visual Studio. Hence ....Access.

Nor me but in any case end users wouldn't cope if it wasn't Access FE

Am I right to assume that you're saying (for you) it's more about improving response time (whether the problem is related to a dodgy network or server processing time) than about best practice per se' ?

Correct. If it ain't broke, don't fix it ...

My thinking is that it would be easier (and FAR more secure) to maintain the code on the server side, than having it in the FE. Or is it a case of not being able/practical to have ALL the code on the BE for some reason?

Certainly not easier to do all code on the server ... at least for me.
As I have about 400 end users, the FE HAS to be Access

As for security, you could use an accde FE
The disadvantage is having to create 2 versions for 32-bit & 64-bit Access if that's relevant to you
Because that is an issue for my clients, I stick to accdb versions & BOLT them down so users can't get to the code.

It works for me & them.

 

[pbaldy]

My experience is similar to Paul's except that I do use SSMS to manage the SQL backend

FYI, SSMS <> SSIS.

I use SSMS all the time.

 

[isladogs]

Sorry - thought it was a typo.

What's SSIS when its at home?

 

[pbaldy]

One answer to the tool question:

https://en.m.wikipedia.org/wiki/SQL_Server_Integration_Services

 

[isladogs]

SSMS 2012 & 2014 Express Edition includes the SQL Server Import & Export Wizard.
I've used it & its good.
However you can't save the wizard settings for later use in the Express version

Not sure whether other SSIS features described in that article are in SSMS as I don't know what they mean

 

[liddlem]

Hmmm...interesting. Thanks PBaldy. I will 'suss' out SSIS

 

[pbaldy]

I just noticed there's also SSMA, which may be more appropriate:

https://docs.microsoft.com/en-us/sq...er-migration-assistant-for-access-accesstosql

Which I haven't used either.

 

[isladogs]

I just noticed there's also SSMA, which may be more appropriate:

https://docs.microsoft.com/en-us/sq...er-migration-assistant-for-access-accesstosql

Which I haven't used either.

Thanks Paul.
I've just downloaded it for future use

Any more members of the SS.. family out there that I also don't know about?

EDIT:
To answer my own question, there's also : SQL Server Data Tools (SSDT)