Access 2003

[metcalf.p]

I have created a Production Tracking database for my company using
Access 2003. Everyone here uses Access 2000, so I have saved the file
as an access 2000 database. I am importing three tables from an ODBC
connection. These tables are being update by another department
constantly. I am using a centralized form for users to view the
information.

If the users are viewing the information and we try to update the
tables they are viewing, we get an error. My question is: Is there a
way around this problem?

This what I have done:
In the module I wrote, I am importing the tables, then renaming them
to the tables the users are viewing, and then altering the structure
of one of the tables. We cannot use linked tables, the reason is we
have only three licenses to access the software through the ODBC
connection. So as soon as three people open the access database they
take those three connections.

Any Ideas?

Thanks,
Phil

 

[Jeff Boyce]

You don't mention what the error message says...

I'm having trouble visualizing your design. It sounds like you (re-)import
the three tables from your ODBC connection, but are saving the .mdb as an
Access 2000 version.

It sounds like you are modifying the tables (names, structure) after import.

Another approach, if I've described your situation, would be for you to keep
a link to the ODBC tables, then create queries that load this (temporary
import) data into three permanent Access tables. This way, the users would
only need to see the three permanent tables (and even then, only via forms,
not directly messing with the tables themselves!). You could set up the
naming and structure the way you want it, and handle the parsing from
original data in your queries, "behind the curtain".

You don't mention whether your database is split or not. Hopefully, your
users each have a front-end file sitting on their desktops, and your
back-end data file sits on a server.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[metcalf.p]

You don't mention what the error message says...

I'm having trouble visualizing your design. It sounds like you (re-)import
the three tables from your ODBC connection, but are saving the .mdb as an
Access 2000 version.

It sounds like you are modifying the tables (names, structure) after import.

Another approach, if I've described your situation, would be for you to keep
a link to the ODBC tables, then create queries that load this (temporary
import) data into three permanent Access tables. This way, the users would
only need to see the three permanent tables (and even then, only via forms,
not directly messing with the tables themselves!). You could set up the
naming and structure the way you want it, and handle the parsing from
original data in your queries, "behind the curtain".

You don't mention whether your database is split or not. Hopefully, your
users each have a front-end file sitting on their desktops, and your
back-end data file sits on a server.

--
Regards

Jeff Boyce
Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/

Microsoft Registered Partnerhttps://partner.microsoft.com/

Sorry for the vagueness. Actually let me clarify.
All we are doing is importing three tables.

The function I mentioned works like this:

Get the three tables
Rename these tables to original name (access appends the names of the
imported tables with a 1)
Alter the last imported table

The function I mentioned works as long as no one is viewing the table
information through
the user form, as soon as anyone looks at the information in that form
the function fails (I
assume because it is trying to write information to the table that is
being accessed).

I have not yet split the database I am testing to make sure things are
working.

Is there anything else I need to provide you with?

Any help is greatly appreciated.
Thanks,
Phil

 

[aaron.kempf]

it would be most appropriate to use Access Data Projects; and then you
could use 'NOLOCK' query hints to make sure that you wouldn't have
blocking problems

but most likely with ADP right out of the block you wouldn't have any
blocking problems

 

[Jeff Boyce]

Phil

Thanks for the clarifications. (I still don't know what your error message
says.)

I'll stick with my first suggestion. Rather than using a function that
modifies table structure after import, were this my task, I would keep a
permanent ODBC link to the source information, and, as needed, run parsing
queries that append/update data in permanent tables.

This ensures that I've designed the permanent table structure to be
well-normalized, regardless of the state of the import data. One very good
reason for this is because Access' features and functions work best with
relational data. Another reason for periodically updating the permanent
(i.e., "user") tables via query may be that the underlying definitions of
those permanent tables are not changing, so you wouldn't need to be
refreshing any link to them. And you would not need to even show the user
the ODBC link -- you could hide that table, in case you have curious users.

There'd be no reason not to add a command button that the users can click on
a form to run the sequence of queries (either as a macro that runs them all,
or as a function that runs them all) to refresh the data. And you can
decide that you will add code to periodically (on start up, on shut down,
once per hour, whatever) do the refresh.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[metcalf.p]

Phil

Thanks for the clarifications. (I still don't know what your error message
says.)

I'll stick with my first suggestion. Rather than using a function that
modifies table structure after import, were this my task, I would keep a
permanent ODBC link to the source information, and, as needed, run parsing
queries that append/update data in permanent tables.

This ensures that I've designed the permanent table structure to be
well-normalized, regardless of the state of the import data. One very good
reason for this is because Access' features and functions work best with
relational data. Another reason for periodically updating the permanent
(i.e., "user") tables via query may be that the underlying definitions of
those permanent tables are not changing, so you wouldn't need to be
refreshing any link to them. And you would not need to even show the user
the ODBC link -- you could hide that table, in case you have curious users.

There'd be no reason not to add a command button that the users can click on
a form to run the sequence of queries (either as a macro that runs them all,
or as a function that runs them all) to refresh the data. And you can
decide that you will add code to periodically (on start up, on shut down,
once per hour, whatever) do the refresh.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank You for your response Jeff. Sorry the error message is user-
defined not system defined.
The only thing I can tell you with regards to the error is the tables
are not updated when I check them.
I think that would be the best way, we kind of set those three tables
as linked tables.
But the problem was that we were taking up more than three licenses
with the users
accessing the information through our form. Would splitting the
database fix this issue,
because the users are only opening the front end?

Thanks Again.

 

[Jeff Boyce]

I don't know the terms of your license agreement. It may be that having
only one back-end .mdb file linked to the ODBC source falls within your
licensing requirements.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[metcalf.p]

I don't know the terms of your license agreement. It may be that having
only one back-end .mdb file linked to the ODBC source falls within your
licensing requirements.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I will try that thank you Jeff.