Access .adp or accdb 4

[PaulBricker]

I'm looking for a little advice on what might be the smarter development platform for a FE database. The situation is we have an SQL database that controls all our card-access door locks. The card-access database allows us to enter users and modify data, but it falls short when it comes to any kind of bulk updates (which we need to do a few times a year). What I have done is create a .accdb database that links to the tables via ODBC and allows me to create forms that work more efficiently for bulk updating. All this seems to work just fine but I have a couple questions about the setup.
1. Would a .adp database work more efficiently? You still connect via ODBC, but the tables are not linked.
2. Would using pass through queries in the .accdb format be more efficient than the ODBC linked tables and queries I use now?
3. The linked tables in the .accdb database cannot be modified and I like that from a security standpoint but the data in the tables is still vulnerable. Would using pass through queries for my forms be a better way of going? I'm not real concerned about staff seeing or messing with the data, but if I can make small adjustments that will raise the level of security, then I would be happy to do that.

Any suggestions welcome.

Paul

 

[dhookom]

1. I would not use ADPs. It seems from my interaction with MS Softies and others, the linked tables is the future.
2-3. Pass-through queries are more efficient. I would probably use some DAO code to modify the SQL property of the p-t queries to allow filtering of the records returned to the front-end. Data retrieved through p-t queries is read-only.

p-t are great for report record sources. Keep in mind a p-t can't use any local table data. You can also execute stored procedures in p-t queries.

Duane
Hook'D on Access
MS Access MVP

 

[RoyVidar]

Some thoughts...

One thing to consider, is that since it's arrival in the Access 2000 version, about the only thing Microsoft has added to adp's, are new bugs;-) And, though not deprecated in the 2007 version, one get the impression it won't take long. The current MS recommendation, is ODBC linked tables.

Doing stuff with linked tables, means some of the query processing will probably have to happen on the client.

Doing pass-through queries, the processing will be conducted on the server, which might be faster then processing on the client side.

There's an additional alternative, since you mention bulk updates. You can create stored procedures on the server, which you can call/execute with ADO (dunno if it works with DAO, too).

For the security, you can use the security model of the SQL server, which I'm lead to believe is far better than Access/Jet (also since ULS is removed from 2007 version), but I've never played around with it.

Roy-Vidar

 

[PaulBricker]

Thank you both for the ideas. Some tables have millions of records but we, as a general rule, don't have to deal with those. My problem is with card access permissions. The application is set up in such a way that in order to update an individuals permissions, it can take 40-60 mouse clicks (I've actually counted them).
I think I will stay with the linked tables. The queries should never return more than 800 records +/- so if the processing has to happen on the client it's not much.
I had hoped to just use the pass through queries in the FE and pull the data from the BE without having to link the tables. This would have kept them out of sight and allow me to populate the forms that way, but I did not realize that they were read only so that will not do what I need. I probably don't realize a few other things also, and that's why I was asking.
I appreciate the time you took to answer this. Any other thoughts are always welcome.

Paul

 

[dhookom]

You can set tables to hidden or change their names to begin with "usys" to [blue]"keep them out of sight"[/blue]. This will keep out those users who don't really want to see the table.

Duane
Hook'D on Access
MS Access MVP

 

[PaulBricker]

I will look at setting the tables to hidden. There are a lot of tables I don't have any use for so once I sort those out it will reduce the size of my db dramatically. Then if I just create a switchboard for their forms, it's not likely they will venture into the guts of the db very much. They aren't snoopy that way. Now if you spend too much time talking with the new down the hall, that's another story.

Thanks again.

Paul