Database or Project?

[kingnothing]

Hi,
I'm in the process of designing a database application for one of our clients.
They already run a SQL server for other purposes. I was wondering if it is a
good idea to do a project with SQL connectivity and Access front end.
Firstly I dont know if i have got it right....When you create an access
project, will you will be linking to a SQL Server and running off from a
database on the SQL???
If it is better to go with SQL, please tell me what other complications i may
face in the future??

Thank you for you time and help

kingnothing

 

[Rick Brandt]

Hi,
I'm in the process of designing a database application for one of our clients.
They already run a SQL server for other purposes. I was wondering if it is a
good idea to do a project with SQL connectivity and Access front end.
Firstly I dont know if i have got it right....When you create an access
project, will you will be linking to a SQL Server and running off from a
database on the SQL???
If it is better to go with SQL, please tell me what other complications i may
face in the future??

Thank you for you time and help

You are making a bit of a false choice here. I would certainly agree that when
an existing SQL Server is already set up and available that using that would be
much better than storing your data in an MDB file.

However; one does not necessarily have to use an ADP project in order to work
against a SQL Server back end. You certainly CAN go that route, but you can
also use ODBC to link tables from and otherwise connect to the SQL Server and
still use an MDB file. This latter method is simpler and preferred by many who
have experience with both approaches. You can read up on the differences and
then make that decision.

One future consideration is if the back end might ever be moved to some other
server database besides SQL Server or even moved back to an all-mdb application.
With an ADP you would be pretty much starting all over. With an MDB such a
conversion can be fairly trivial.

 

[kingnothing]

Thanks for your help Rick.

I will definitely look into the differences and go with the mdb and SQL
server with ODBC.
Also, if there is any definitive guide (online) for access, please let me
know. I have developed access databases before, but it's been some time.

Regards,

kingnothing

 

[kingnothing]

.....sorry one more thing..

How does it work?? I just create a blank database, copy the mdb file to the
sql server, share the folder, map a network drive to it, and use a DSN to
connect to that mdb??? am i right??? or is there anything else to it??

 

[Rick Brandt]

....sorry one more thing..

How does it work?? I just create a blank database, copy the mdb file to the
sql server, share the folder, map a network drive to it, and use a DSN to
connect to that mdb??? am i right??? or is there anything else to it??

Ack, no!

You create the MDB on your PC. On that PC you create an ODBC DSN and then use
that to create links to the tables on the SQL Server (with code the links can be
made without a DSN). Then you build your app around those links and in some
cases passthrough queries which use the same ODBC connection.

When your app is finished you create an MDE from it and give copies of that to
all your users who each keep a copy on their local drives. Users should not
have file access to anything on a SQL Server box.

 

[Rick Brandt]

Thanks for your help Rick.

I will definitely look into the differences and go with the mdb and SQL
server with ODBC.
Also, if there is any definitive guide (online) for access, please let me
know. I have developed access databases before, but it's been some time.

Lots of FAQ type resources available by Googling. As far as a comprehensive
on-line tutorial I don't know of any.

The main thing to keep in mind with client server is to minimize LAN traffic.
You don't achieve that with an app built with the wizards. Even most of the
help file samples are terrible in this regard (as is the northwind sample
database). At every step you should strive to retrieve the fewest bytes
possible for the task.

For example a simple form bound directly to a large linked server table is a
terrible thing to have unless properly managed with filters to restrict the
number of records returned. Let that open at record 1 of 300,000 (not really
very big) and allow the user to play with the "Find" button and he could bring
the LAN to its knees all by himself.

 

[kingnothing]

Thanks Rick

 

[kingnothing via AccessMonster.com]

Ack, no!

You create the MDB on your PC. On that PC you create an ODBC DSN and then use
that to create links to the tables on the SQL Server (with code the links can be
made without a DSN). Then you build your app around those links and in some
cases passthrough queries which use the same ODBC connection.

When your app is finished you create an MDE from it and give copies of that to
all your users who each keep a copy on their local drives. Users should not
have file access to anything on a SQL Server box.

Is it a File/System/User DSN?? I'm guessing its a File DSN for now. Now, when
you say use that to create links to tables on the SQL server, how do you do
that??? the mdb is on my local pc...
Forgive me for being such a pain, i'm a noobie when it comes to these things..
...

 

[Rick Brandt]

Is it a File/System/User DSN?? I'm guessing its a File DSN for now.

I typically use System DSNs, but for single user PCs it doesn't make much
difference.

Now, when
you say use that to create links to tables on the SQL server, how do you do
that??? the mdb is on my local pc...
Forgive me for being such a pain, i'm a noobie when it comes to these things..

File - Get External Data - Link tables
File Type = ODBC
Select your DSN (previously created)
Select your tables

 

[kingnothing via AccessMonster.com]

Thanks Rick

....sorry one more thing..
[quoted text clipped - 15 lines]

Is it a File/System/User DSN?? I'm guessing its a File DSN for now.

I typically use System DSNs, but for single user PCs it doesn't make much
difference.

Now, when
you say use that to create links to tables on the SQL server, how do you do
that??? the mdb is on my local pc...
Forgive me for being such a pain, i'm a noobie when it comes to these things..

File - Get External Data - Link tables
File Type = ODBC
Select your DSN (previously created)
Select your tables

 

[kingnothing via AccessMonster.com]

....sorry one more thing..
[quoted text clipped - 15 lines]

Is it a File/System/User DSN?? I'm guessing its a File DSN for now.

I typically use System DSNs, but for single user PCs it doesn't make much
difference.

Now, when
you say use that to create links to tables on the SQL server, how do you do
that??? the mdb is on my local pc...
Forgive me for being such a pain, i'm a noobie when it comes to these things..

File - Get External Data - Link tables
File Type = ODBC
Select your DSN (previously created)
Select your tables

HI Rick, I tried what you said, now i get an error message like this: "you
cannot use ODBC to import from, export to, or link an external microsoft Jet
of ISAM database table to your database". I think i'm doing something wrong...
..

kingnothing

 

[Rick Brandt]

....sorry one more thing..

[quoted text clipped - 15 lines]

Is it a File/System/User DSN?? I'm guessing its a File DSN for
now.

I typically use System DSNs, but for single user PCs it doesn't
make much difference.

Now, when
you say use that to create links to tables on the SQL server, how
do you do that??? the mdb is on my local pc...
Forgive me for being such a pain, i'm a noobie when it comes to
these things..

File - Get External Data - Link tables
File Type = ODBC
Select your DSN (previously created)
Select your tables

HI Rick, I tried what you said, now i get an error message like this:
"you cannot use ODBC to import from, export to, or link an external
microsoft Jet of ISAM database table to your database". I think i'm
doing something wrong... .

kingnothing

I thought your tables were on SQL Server. When you create your DSN you need to
use the SQL Server ODBC driver and point it at your server and database. You
cannot use ODBC to connect JET (mdb) tables to an Access front end.

 

[kingnothing via AccessMonster.com]

Oh OK...sorry i had mistaken...i thought i was supposed to create an mdb and
link an mde to that through ODBC

[quoted text clipped - 23 lines]

kingnothing

I thought your tables were on SQL Server. When you create your DSN you need to
use the SQL Server ODBC driver and point it at your server and database. You
cannot use ODBC to connect JET (mdb) tables to an Access front end.