Putting data in SQL server ?

[mscertified]

I'm supporting a heavily used application and I'm feeling the need to move
the data into SQL server. The main reason being that we want a web interface
to the data. To a lesser extent, because of data volume. This application
has around 100 queries and who knows how many VB-built SQL statements. I
realize these all have to be converted to stored procedures. I'm thinking it
would not be too big a deal to do this. What other considerations are there
regarding such a conversion?

 

[Jamie Collins]

I'm supporting a heavily used application and I'm feeling the need to move
the data into SQL server. The main reason being that we want a web interface
to the data. To a lesser extent, because of data volume. This application
has around 100 queries and who knows how many VB-built SQL statements.

You could use SQL Server Profiler to discover the ad hoc SQL
statements.

I
realize these all have to be converted to stored procedures. I'm thinking it
would not be too big a deal to do this.

Sounds like a non-trivial task to me.

Jamie.

--

 

[Sylvain Lafontaine]

By using linked tables and add the parameter dbSeeChanges here and there
where required (Access will tell you where you need to do so when you will
try to run the code), you can transfert your data to a SQL-Server and use it
as a backend from a MDB frontend in no time (well, a few hours is a more
exact evaluation). From there, you could build other interfaces like your
web interface while keeping the MDB frontend.

The only problem you might have might be a speed problem; as there is a
possibility that this will be slightly slower then using a MDB as the
backend. If you want to correct this problem, you might want to start
looking a things such as using Views, sql passthrough queries, ADP, unbound
forms, .NET, etc. (listed in order of increasing complexity/speed). For
updatable views, see:

http://support.microsoft.com:80/kb/q209123/

For a good point to start, see the newsgroup m.p.a.sqlupsizing,
mpa.odbcclientsvr and mpa.externaldata. For the remaining, start
searching/studying/trying.