Question How to obtain the scheme of an Access Database?

[lok1234]

Dear all,

I have an Access Database with about 30 tables (include link tables) and 10 queries.

Could I export the schemes of these 30 tables and 10 queries so that I can import these schemes to the production database?

Many thanks

 

[ajetrumpet]

what is a scheme? isn't that an XML term or something?

 

[jsv2002]

Assuming its just your wording of "schemes" is incorrect and you mean import the database objects e.g. Tables/Queries into another database then open the database you want to import to select file>get external data>import use the resulting dialog box to select the path of the database you want to import from then a menu will appear showing the available objects to import.

Select/highlight your desired objects and click ok (the options button will give you extra choises such as import data or structure only) and away you go.

good luck John

 

[lok1234]

hi all,

The scheme that I mean is something like create database, create table ..index etc scripts. I believe John has got what I mean.

But John, I would like to keep a script file (a text file) instead of "import" the objects from development database to production database. SQL Server has something like "export the scheme to a script file".

Is there any similar method in Access?

 

[Banana]

Unfortunately, no. Access doesn't have a mean of generating DDL scripts to do what you just asked for.

I suppose it may be possible to build one yourself by writing a block of code to interrogate the objects and express it as a DDL statement.

Can you just export it as text file? It's not DDL but at least it'll be text...See this thread.

 

[lok1234]

Unfortunately, no. Access doesn't have a mean of generating DDL scripts to do what you just asked for.

I suppose it may be possible to build one yourself by writing a block of code to interrogate the objects and express it as a DDL statement.

Can you just export it as text file? It's not DDL but at least it'll be text...See this thread.

Many thanks.
I have tried the script.
Actually, I don't really understand the contents of "exported" text files. I could not find any "create table" statement in the table object text files, which i suppose it will contain.

Besides, there is an import error which say: " You canceled the previous operation"

 

[jsv2002]

Besides, there is an import error which say: " You canceled the previous operation"

This is usually due to a spelling mistake referencing an object or not correctly identifying a field type such as text instead of numerical field.

John

 

[Banana]

Actually, I don't really understand the contents of "exported" text files. I could not find any "create table" statement in the table object text files, which i suppose it will contain.

Well, I did say there is no direct support for DDL scripts so of course you wouldn't get a "CREATE TABLE" or any DDL syntaxes in there. It's useful for importing between two Access database, but now I am starting to think you want to get DDL script so you can execute it in a different backend?

If that's the case, then you want to rebuild the tables from scratch using the backend's native dialect to get the most out of it. You can then import the data into tables directly, by getting a CSV file of the data or doing a linked table in Access and updating.

If you have SQL Server, you can try upsizing and see how well it exports the table definition, but don't be disappointed if it get some things wrong.

 

[lok1234]

Well, I did say there is no direct support for DDL scripts so of course you wouldn't get a "CREATE TABLE" or any DDL syntaxes in there. It's useful for importing between two Access database, but now I am starting to think you want to get DDL script so you can execute it in a different backend?

If that's the case, then you want to rebuild the tables from scratch using the backend's native dialect to get the most out of it. You can then import the data into tables directly, by getting a CSV file of the data or doing a linked table in Access and updating.

If you have SQL Server, you can try upsizing and see how well it exports the table definition, but don't be disappointed if it get some things wrong.

thanks. Actually, I face a problem:

I have a production Access Database and a development Access Database.
As you know, development database is always changing (e.g. add some fields in a table, change the datatype of a field etc..).. I always have to "record" the changes down and then apply the changes in the production database. But this is really a harsh task as I always "miss" some changes and forget to apply the changes in production database. The outcome is, of course, it has runtime error when user work in the production database.

Therefore, one of my want is, I want to "diff" the difference between the development access database and production access database, and then apply the "changes"...

Do you have any idea of how to do it? (in case it need third party tool..please recommend to me.)

Many Many thanks

 

[lok1234]

This is usually due to a spelling mistake referencing an object or not correctly identifying a field type such as text instead of numerical field.

John

thanks, but I just "import" the text file generated by the "export" function. When I import the exported text file, the above error was shown

 

[Banana]

Short of a DIY solution, this is one I know about: FMS's Access Detective

The project I am currently doing basically has three .mdb; Development, Test and Production. The idea is that we develop in Development, version it for every day and when we reach a state where we want to be at, we import the changes (which we handwrite or record somehow) into the test. It's not one code-line by code line or one field by field but rather by objects (e.g. import in the changed tables, modules, forms), test it and if everything works, copy the Test into the new Production.

 

[lok1234]

Short of a DIY solution, this is one I know about: FMS's Access Detective

The project I am currently doing basically has three .mdb; Development, Test and Production. The idea is that we develop in Development, version it for every day and when we reach a state where we want to be at, we import the changes (which we handwrite or record somehow) into the test. It's not one code-line by code line or one field by field but rather by objects (e.g. import in the changed tables, modules, forms), test it and if everything works, copy the Test into the new Production.

good ... many thanks..
but it need money.. , my company surely ban my idea.