Should I convert mdb to adp?

G

Guest

My knowledge of Access is limited to .mdb desktop database (no VB or SQL
knowledge). I needed to create a report. In order to do so, I had to create
4 queries to merge and filter data based on selection parameters. I have
several forms and macros to facilitate the process. My problem is that my
friend that said he originally only wanted the report to run from his local
PC now wants others in his company to run the report. He tried creating
links to the macro at other employees PC and once multiple users started
using the macro, the temp files were not there when needed. Do I need to
convert my .mdb to .adp and will this solve my temp file problem? I have
tried the upsizing utility, however not very familiar with VB or SQL. I was
wondering if there was an easier way for multiple users to use .mdb. The
other users do not have Access on their own PCs. I appreciate any help you
can give.

Sincerely,

Denise
 
D

Douglas J. Steele

Unless the users have Access on their PCs, they will be unable to use any
MDBs, MDEs, ADPs or ADEs.
 
G

Guest

I just found out that they do have Access on their PCs. So, if they run my
macro that deletes temp files that are created in later called queries, will
two users running the macros and queries delete and/or try to create temp
files and create errors? The macros, queries and reports are on the server.
Are the temp files created on the server too? If two users will collide in
using these temp files, how would I get around this?
 
G

Guest

V

Vinayak N

Hi Denise

Converting it into .adp won't be of much help.

However I don't understand what you mean by temp files here.

If you have an access mdb and need to share it among multiple users
without conflicts taking place then:
1. Place the mdb file on the server.
2. In the database Options>Advanced>set the default open mode to
Shared, and default record locking to No Locks.
3. Also create a shortcut for the database and place the same on the
users respective machines.
4. The target of the shortcut should be "path of the msaccess.exe of
their machine" "path of the database residing on the server"

This should work and no conflicts should be reported hopefully.

Regards,
Vinayak
 
G

Guest

Hi Tom,

Can I do this without having to convert my queries to SQL or my macros to VB?

Denise
 
G

Guest

Hi Vinayak,
Converting it into .adp won't be of much help.
Click to expand...

Actually, that would solve the problems associated with sharing the entire
application, since one cannot share a .adp file. However, one can also not
create temporary tables *in* a .adp, so Denise's application logic would
likely need to be reworked. Microsoft is not really encouraging folks to go
the .adp route any longer these days.
However I don't understand what you mean by temp files here.
Click to expand...

I'm thinking she meant temp tables within the .mdb file.

If you have an access mdb and need to share it among multiple users
without conflicts taking place then: ....
Click to expand...

You still need to split the database to avoid problems. Each user needs to
be running their own local copy of the front-end (FE) database.

Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
G

Guest

Hi Denise,

Any queries are already saved as SQL statements. Access does this for you,
under the covers. To see this, just open any saved query in design view. Then
click on View > SQL View. You do not need to upsize your database to SQL
Server, if that is what you were asking, unless performance and/or security
issues need to be addressed.

You also don't need to convert any macros to VB.
However....

I suggest that you strongly consider doing so. In Access 2003 and all prior
versions, one cannot trap for errors and handle them gracefully with macros.
Instead, an error will result in a really ugly macro error dialog that your
users will be presented. The newest version of Access, Access 2007 currently
in beta release only, *does* allow for error trapping, so macros will be more
appropriate for a shared Access application with Access 2007.

Consider the following quote:

From: "Inside Relational Databases, 2nd Edition, by Mark Whitehorn and Bill
Marklyn, published by Springer, p 151)

"Macros offer the next level down, extending the functionality of the GUI.
Macros are still limited, however, and do not provide anything like the
enormous flexibility of a programming language. Both the macro and the
programming languages take some effort to learn and, surprisingly, often
require relatively different skills; in other words, a good working knowledge
of macros may not make it much easier to convert to using the programming
language. Perhaps even more surprisingly, I do not believe that programming
is fundamentally more difficult to learn. Macros are easier to use but not by
orders of magnitude."

"If you are new to RDBMSs, I suggest (with as much deference as possible)
that you may well not be in a position to judge whether you need macros or
programming. In that case, my advice is clear. Unless you are sure that your
needs really are simple, don't bother learning to use macros. Once you find
that you need more than the GUI offers, go straight to the programming
language. In this way you avoid the pain of climbing one learning curve only
to discover that the view from the top is unsatisfactory and another climb
awaits you."


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
G

Guest

Hi Tom and Vinayak,

The temp files are actually temp tables that I create in several Make Table
Queries. I have to smoosh several files together with selection criteria and
this is the only method I can come up with to have later queries continue to
filter the data and use in the report.

I think that by splitting the database will probably work for this. I am
assuming that this will result in me installing my queries, macros and report
on each machine that needs to run the report, therefore creating the temp
tables on each users machines, thus avoiding the collision of temp tables
used my multiple users.

Thanks for all your help!! You are awesome!

Denise
 
G

Guest

Hi Denise,

You are correct, except that we do not refer to installing "queries, macros
and report(s)" as separate objects. The front-end (FE) of a split .mdb
application contains *all* queries, forms, reports, macros, modules and any
non-shared data in local tables. The back-end contains only the shared tables
(typically).

Here is a very cool method of using temporary tables in Access (.mdb)
applications, without causing the bloat that is typically associated with
doing so in FE files:

CreateTempWorkDB.zip (29 KB)
http://home.comcast.net/~tutorme2/samples/tmpwrkdb.zip


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to convert mdb -> adp ? 6
ADP or MDB 5
mdb to adp 9
mdb to adp subform problem 3
Alternative to GoToRecord acNewRec for Access ADP 3
convert mdb to adp 0
adp vs mdb 9
mdb v. adp inquiry 1

Top