ADP – Yea or Nay? (Access2000)

Topic

New Reply

I am creating a database for 70 periodic users. I will have no more than 25,000 records in the database. I have the option of setting the tables up in SQL Server on a server at a nearby, separate location. I have always created my databases with one mdb file as the data file and another mdb as the interface. I have never used ADO nor any other form of explicit connection. I have used ODBC for DB2 and other external tables. I have never created an ADP file. With those ‘givens’, I’m hoping to get some general advice on the optimal approach. I own Acc2000 Developer’s Handbook Vol2 and it provides direction but I am still hazy about what approach is favored with developers and what approach is the optimal one. I have no problem breaking out of my mdb comfort zone but I want to make sure I am making the right jump.
As always…thanks!!

Reply | Quote

Replies

If you aren’t comfortable with ADO, then avoid the ADP. I tend to avoid ADPs anyhow if I’m turning the database over to users. I’m just not comfortable with letting them get that close to the SQL Server back end without a LOT of protections. You can compile it into an ADE, of course, but I like the distance that an MDB offers to keep my users from shooting themselves (and me) in the foot.

There’s no reason you can’t use an ODBC connection with linked SQL Server tables and an Access mdb as a front end if that’s what you want to do. The big question is how many of those 70 “periodic” users would be in the database at any one time. With only 25,000 records in the database, there isn’t any overwhelming need to move to SQL Server unless the number of simultaneous users requires it or unless the size of the database will grow *much* larger than that in short order.

There’s also the question of who would administer the SQL Server database. Although the upsizing wizard makes it look simple, it isn’t. There are some things you simply have to do from SQL Server (like creating stored procedures, creating new SQL Server tables, changing table designs etc.), and those are not always as straightforward as they might first appear.

Reply | Quote

Thank You Charlotte. I just want to make sure I’m not forcing an MDB to do something that would be better served by an ADP…your notes make it clear I am on the right path using an MDB. It seems like ADP’s throw a lot of extra steps itno the formula. I just wish I had a better grasp of when I should upgrade.

There will be maybe 20 simultaneous users. My client will be using the database on another client’s server. He is afraid of storing the front-end and back-end on his client’s server. Hence the reason for SQL…to keep the data remote so his client can’t take the database and squeeze him out. Someone already started work so I’ve been trying to persuade him to move everything to his client’s server. I plan on locking everything down (MDE) to alleviate his concerns. If you have any recommendations on this issue, I would apprecite reading them. Also, is there a way to lock tables down so someone can’t import them into another table? On MDE files I can still go in and import the data.
Once again…Thanks!

Reply | Quote

ADPs are wonderful tools, but if you aren’t familiar with them and with SQL Server, the learning curve can be very steep. If you’re using a SQL Server back end, security on the tables is handled through SQL Server roles, not through Access at all; and if you have 20 simultaneous users, you’d better have a SQL Server backend. I’m not sure what you mean by locking them down so someone “can’t import them into another table”, so I don’t know how to answer the question.

Reply | Quote

Is there a way to keep someone from creating a database and importing tables from an MDE file to the new database? The forms in the MDE are safe since they’re compiled, however, people can still import the tables.

Reply | Quote

MDE protect your code and forms and reports design.
To protect data, you will have to use User Security.
You can use the User-Level Security Wizard to protect your mdb or mde.
Have a look at Frequently Asked Questions About Microsoft Access Security for Microsoft Access versions 2.0 through 2000
to learn how to use User Security.

Reply | Quote

Francois,
Outstanding…thanks a bunch!

Reply | Quote

I thought you were planning to use SQL Server as a backend. What tables are you talking about importing?

Reply | Quote

Sorry about the confusion. I’m just trying to find a way to keep someone from importing the tables in my database into their own database.

Reply | Quote