Temporary Table names

[davebhoy]

Hi there

I'm new to this so please be patient!

I have developed an Access database which produces financial reports based on a date range which the user specifys, taking the data from a SQL server. He chooses his dates and then runs a macro which creates the new tables, overwriting previous tables. Numerous financial reports run on these tables so I need to keep the table names the same.

The problem is that only one user can access the DB at anytime because the new user can't delete the other users table becuase it may be based on a different date range.

I'm open to suggestions but maybe what I need to so is create tables which are based on the user id when he logs in to the Access database so that he doesn't delete someone elses table. How can I do this please!!!???

Thanks in advance

Davebhoy

 

[boblarson]

This is where a separate front-end for each user would be handy. No need to code differently for any user, but they could create their own temp tables.

As for updating the front-ends when there are changes, check out this tool that I posted.

Access Front-End Auto Updating Utility

 

[davebhoy]

Temporary Table Names

Thanks!

I a bit of a novice and therefore maybe naive but is there any way to avoid users having MS Access and ODBC connections on their pc?

If I appended data using the username will users run in to errors if someone is appending or deleting their own data while someone else is in the table accessing their own data. Each user would have data belonging to their username?

Thanks again in advance

Dave

 

[The_Doc_Man]

is there any way to avoid users having MS Access and ODBC connections on their pc?

1. Look up something (on the web, not Access help) called CITRIX as a way to avoid ODBC and Access on every machine. But be aware that Access is not normally licensed for the CITRIX environment and therefore such usage might constitute a violation of the license agreement. But I don't know what kind of setup you have.

2. Given that your DB is actually on an SQL server and Access is just on the front end, ODBC on every machine is required if you don't have CITRIX. Not that CITRIX is a panacea either.

3. You can do without Access only if someone develops the front end using the MS Access developer kit - which is a separate purchase item. You can freely distribute the run-time portion of that kit plus your customized front-end database. This costs money and takes more time than to just buy a few Access kits without Office. So you have to balance the cost of "multiple copies of Access + distribute ordinary MDB file" vs. "time to develop + cost of developer's kit"

4. If you don't have Front-End/Back-End split in your database but instead have the SQL Server tables just linked via ODBC as a "second table" situation, you can write queries to create local (therefore private) copies of a given table that are NOT shared at the workstation level. Though their source tables are certain shared on the SQL server. Then you don't have to worry about name overlaps because all copied tables are private.

 

[davebhoy]

OK thanks!