Converting from mdb back end to SQL

[Karen Hart]

My Access XP application has a msaccess runtime front end and an msaccess
mdb back end. The back end consists of tables and relationships, and rights
dictated by the workgroup administrator secured.mdw.

I have a client that wishes to use a unix compatible SQL database for the
back end. Their tech wants to build it, and he is requesting my backend
database "schema" so that he can build a back end identical to my back end
that is an msaccess mdb.

Is a "schema" what I need to provide him? If so, how do I do that?
If not, what should I be providing him, and how would I do that?

Thank you so much in advance,
Karen

 

[Dave]

well, to duplicate the backend he would need each of the table's column
definitions so he could duplicate the tables. then he would also need to
know the relationships you have defined. i haven't tried this but i think
the proper method to get access to his tables is to define an odbc data
source for his database and then change the links in your front end to point
to the odbc source. you might have to remove the linked tables and relink
to his tables, i don't see an option in the linked tables manager to change
to an odbc source once you are linked to an mdb back end.

 

[Karen Hart]

I am aware of all of this. My question is how do I print out the details of
the back end?

 

[Immanuel Sibero]

Karen,


The only thing close to a "schema" in Access would be the printout of the
Documenter. (Tools>>Analyze>>Documenter).
Beginning Access 2002 (if I'm not mistaken), you can print the relationship
diagram.

When doing a migration such as what you're going through, you (or the Unix
database guy) should also look into migration tools specific to the backend
server product he's using. I wouldn't be surprised if it included a
migration tool which would read the design of your Access back end (i.e.
your schema) and create a script specific to the server product.

Immanuel Sibero

 

[Karen Hart]

Dear Immanuel ,

Thank you for your knowledgeable answer. I am aware of the documenter. I
thought that would probably be my answer.

I will forward your next comment on to the Unix database guy as well.

What security issues will I be faced with, due to the fact that my back end
has table rights and user names/groups associated with it? Is it possible
to control all that through the front end, and just leave the back end wide
open as far as rights?

Thank you,
Karen

 

[Immanuel Sibero]

Karen,

User security and privileges should be done on the back end. In fact, many
applications initially built in Access (with Access User Security) are
upsized / migrated to a server based back end such as the Unix based you
mentioned and others (SQL Server, Oracle, etc) for this very reason. Access
user security is file based which is no match for server based security
offered by those back end database servers.

What security issues will I be faced with, due to the fact that my back end
has table rights and user names/groups associated with it?

Table rights, user names/groups should probably be recreated using the
server back end security facilities. This means that many of the Access
security features would be moot.
Depending on how much you embed Access User level security in your front end
app, you may have some rewrite your front end to accomodate this. For
example, any VBA code that takes advantage of Access user level security
probably needs a rewrite.

Is it possible
to control all that through the front end, and just leave the back end wide
open as far as rights?

Rolling your own security with your existing Access security scheme? Sure,
it's possible. But again, as you said, the back end will be wide open. Only
users using your front end application will be subject to your front end
security scheme. Users would be able to access the back end data via other
means or applications.

Bottom line is, upsizing an unsecured Access application can be as simple as
exporting all your tables to the server back end and reestablish links to
them. But upsizing a secured Access application can be a mess.... because
now you have different options and each option is not mutually exclusive.


HTH,
Immanuel Sibero

 

[david epsom dot com dot au]

I am not aware of an easy way to get DML from just
Access - you would need a third party tool. Perhaps
if you upsized to SQL Server you could get SQL Server
DML.

However, in A2003 you can Export tables as XML, giving
an XSD file.

He may be able to find a tool that will import XSD.
He will certainly think it is slick if he can.

(david)