Placing Access Data On SQL

[BerkshireGuy]

Our IT department wants to place our Access 2000 tables on an SQL
server due to the fact the tables are quite large.

With that said, can we still use the Access queries or do we have to do
everything via SQL views? I would think we could just create an ODBC
connection to the tables on the SQL server and that is the only thing
we would need to change. Is my understanding correct? Are there any
pros/cons with using the Access queries or going to SQL views? Will
Access have any issues with the datatypes that the SQL tables use?

One concern is that we need read/write permissions to the SQL tables.
Would we need SQL Enterprise Manager for any reasons? What benefit
would the SQL Enterprise Manager give us? I know I could create stored
procedures, but couldnt I just continue using modules within Access?

Thanks
B

[Justin Hoffman]

"BerkshireG uy" <bd*****@yahoo. com> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.com.. .

Our IT department wants to place our Access 2000 tables on an SQL
server due to the fact the tables are quite large.

With that said, can we still use the Access queries or do we have to do
everything via SQL views? I would think we could just create an ODBC
connection to the tables on the SQL server and that is the only thing
we would need to change. Is my understanding correct? Are there any
pros/cons with using the Access queries or going to SQL views? Will
Access have any issues with the datatypes that the SQL tables use?

One concern is that we need read/write permissions to the SQL tables.
Would we need SQL Enterprise Manager for any reasons? What benefit
would the SQL Enterprise Manager give us? I know I could create stored
procedures, but couldnt I just continue using modules within Access?

Thanks
B

That is basically correct. However, you talk only of data, tables and
queries, not forms or coding. If you have written coding which worked with
a jet backend, then it may need modifying to work with sql server.
The easiest thing to do would be to create a test sql database by using the
upsizing wizard and create a front end with linked tables. You shoud
probably go through the resulting database to see what the wizard did in the
upsizing process - and modify accordingly (indexes named wierdly, datatypes
or lengths could be tweaked).
The Enterprise Manager gives you the tools to do a number of things in one
handy place. Of course, not everyone needs it, but this is how you would
manage the whole server, security and logins and make any design changes.
True you could use an Access project to make design changes, but the
Enterprise Manager lets you do a whole lot more.

[Rick Brandt]

BerkshireGuy wrote:

Our IT department wants to place our Access 2000 tables on an SQL
server due to the fact the tables are quite large.

With that said, can we still use the Access queries or do we have to
do everything via SQL views?
"Have to"? No. In some cases it would be more efficient to do so.
I would think we could just create an
ODBC connection to the tables on the SQL server and that is the only
thing we would need to change. Is my understanding correct?
Technically, this is correct, but as stated some queries against links might
not be very optimized for a server environment. However; if you have a
local "expert" who tells you that ALL queries need to be converted to
Pass-Throughs and Stored Procedures then they are definitely incorrect.
Access/Jet does a pretty good job of passing query workload onto the server
even when local queires against links are used. The criteria is how does a
given query perform. If it does fine as a loocal-against-a-link then I see
no reason to change it.
Are there any pros/cons with using the Access queries or going to SQL
views?
Generally if you are doing more complex joins you are better off setting up
a view on the server rather than creating joins in a local query against
links. The complexity of hte query in terms of calcualtions/expressions is
often not an issue, but complex joining is better done with a View or Stored
Proc.
Will Access have any issues with the datatypes that the SQL
tables use?
Yes. When possible I stick with Money instead of Numeric or Decimal. I
also avoid Bit and prefer to use Integers instead, but if you want to use
Bit fields make sure they do NOT allow Nulls. Many of the headaches with
imprecise DataType matching can be avoided by adding TimeStamp columns to
any tables that have such types.
One concern is that we need read/write permissions to the SQL tables.
Would we need SQL Enterprise Manager for any reasons? What benefit
would the SQL Enterprise Manager give us? I know I could create
stored procedures, but couldnt I just continue using modules within
Access?

You will want EM if you need to create or modify the structures on the
server and it is almost inevitable that you will want to do this so I would
definitely install the client tools.

It's a good idea to move logic from a code module to a Stored Proc if it
will reduce network traffic. In other words if the processing logic is
determining which rows of data will be returned then it's better to be on
the server. If the number of rows is determined by the SQL and you just
need to do some processing on them then I see little advantage to moving
that process to the server.

There was a time when the typical client PC was not nearly as capable for
processing data as the server so it made more sense to get as much
processing on the server as possible. Today that is simply not the case as
the typical desktop PC has way more processing power (for a database
application) then most people will ever need. What is key is reducing how
much goes over the wire, not where the processing is done.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[BerkshireGuy]

Rick/Justin,

Thank you both for the prompt detailed replies.

Yes, we have a lot of coding going on in the Access system. Both at
the form level and module level.

Currently our database is on a network folder with a front and back end
(both Access). Ok you can all groan now.

So I am thinking if they go to SQL, then each users should get an
Access front end. This would allow us to link to the SQL tables and
deal with security permissions based on user?

Also temp tables would be handled better because each users has their
own front end. I could eliminate the need of creating temp tables with
tbltemp_userid which is what I have been doing. I had to do this
because the users where using the same front end.

So Enterprise Manager is really not considered a client tool? I would
love to have access to it to create stored procedures, triggers,
schedule DTSs, etc, but I need to make a case to IT to give it to me.
<grin>

Can you pass commands via an Access module that instructions the SQL
server to create a temp table on their side? I would think yes. If
nothing else, I could launch a stored procedure from within Access to
create the temp table.

Sorry, its been a while since I've played in the SQL 2000 environment.
I appreicate the information.

[Justin Hoffman]

"BerkshireG uy" <bd*****@yahoo. com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...

Rick/Justin,

Thank you both for the prompt detailed replies.

Yes, we have a lot of coding going on in the Access system. Both at
the form level and module level.

Currently our database is on a network folder with a front and back end
(both Access). Ok you can all groan now.

So I am thinking if they go to SQL, then each users should get an
Access front end. This would allow us to link to the SQL tables and
deal with security permissions based on user? Without a doubt, you should give each a copy of the front end. You still
need to think about SQL Server security: firstly whether you use the SQL
Server account or a Windows account. Of course, you might not be free to
decide this yourself, but the Windows integrated security can simplify
things for a designer (you don't have to worry about passwords for your
application).
Also temp tables would be handled better because each users has their
own front end. I could eliminate the need of creating temp tables with
tbltemp_userid which is what I have been doing. I had to do this
because the users where using the same front end.
Yes, you can create local temp tables. However, your current approach might
still be suitable with a single table on your SQL Server database. I
suppose it depends what it's for.
So Enterprise Manager is really not considered a client tool? I would
love to have access to it to create stored procedures, triggers,
schedule DTSs, etc, but I need to make a case to IT to give it to me.
<grin> I don't see why they shouldn't. Assuming you have a username and password
that allows it, you could use Access (or even a vbs script written in
Notepad!) to create, edit or delete stored procedures on the server. Or
deletes half the records or perhaps all the tables - whatever. What I am
saying is that the important thing is the permissions your login has - not
the software you have installed. Just because you use EM you don't need
full admin access to the server - your login should allow you to do what you
need and no more.
Can you pass commands via an Access module that instructions the SQL
server to create a temp table on their side? I would think yes. If
nothing else, I could launch a stored procedure from within Access to
create the temp table. Of course. Provided the login has permission, code written in Access could
do anything with any of the server objects.
Sorry, its been a while since I've played in the SQL 2000 environment.
I appreicate the information.