Q: how can I copy my .mdb table into my .adp via VBA

  • Thread starter Thread starter A C
  • Start date Start date
A

A C

Hello

We have a .mdb database, and would like to copy some tables from this into
our .adp via VBA within our .mdb We used to do the copy by hand but the
number of tables is now large and they are regularly changing. We also
found that the copy and paste does not transfer the primary keys and default
values over.

Can someone point me to some code to do this?

We also need the code to loop over the .adp tables and set the primary keys
and default values based on the settings in the .mdb tables.

At a pinch we will accept doing this from within VBA in the .apd, but only
as a last resort - we really want it all run from the .mdb

Thanks in advance
Andrew
 
Are you just copying data from one database to another, or are the table
structures changing?
 
I am sure you know there is NO table in *.adp file. The *.adp is merely a
frontend app of SQL Server/MSDE, containing only forms/reports/code. The
actual tables are on SQL Server/MSDE. MS Access prodives very user-friendly
way to allow you "copy" and "paste" tables/forms... btween *.mdb and *mdb,
and btween *adp and *.adp, and even between *.mdb and *adp. But if you do
not realize what exactly MS Access does, then you let MS Access do too much
for you, especially when you want to copy table from *.mdb to *.adp.

This type of "copying" actaully is to transform a JET database table to SQL
Server database table. (since *.adp itself does not have table, the table is
SQL Server database table). There are some differences between these two
types of database. The usual way to convert a JET database to SQL Server is
to use upsizing wizard in MS Access or DTS in SQL Server (the both are the
same thing, actually). It will lead you through a sieries iof steps so that
you can decide how you want the table in Jet trsformed into table in SQL
Server (for example, text column in Jet will be converted to NVarChar(255),
by default, but you can select VarChar(50) instead). However, if you use
"Copy", you do not have such choice. After copying, you definitely need to
go through the converted table and make sure the conversion is what you
really want. Oh, yes, whether you "copy" the table, or use Upsizing Wizard,
you need to re-set primary key for the table.

My point is converting JET database table to SQL Server database table is a
major change, since the two are different type of database system. Even with
DTS/Upsizing Wizard, it is still required to manually fix something to the
converted database/table. If you only convert databases of specific design,
surely you can write code with great control as to what exactly you want to.
However, why do you have to convert the same *.mdb(s) repeatedly so that is
worth writing code for that?
 
Norman Yuan said:
I am sure you know there is NO table in *.adp file. The *.adp is merely a
frontend app of SQL Server/MSDE, containing only forms/reports/code. The
actual tables are on SQL Server/MSDE. MS Access prodives very user-friendly
way to allow you "copy" and "paste" tables/forms... btween *.mdb and *mdb,
and btween *adp and *.adp, and even between *.mdb and *adp. But if you do
not realize what exactly MS Access does, then you let MS Access do too much
for you, especially when you want to copy table from *.mdb to *.adp.

This type of "copying" actaully is to transform a JET database table to SQL
Server database table. (since *.adp itself does not have table, the table is
SQL Server database table). There are some differences between these two
types of database. The usual way to convert a JET database to SQL Server is
to use upsizing wizard in MS Access or DTS in SQL Server (the both are the
same thing, actually). It will lead you through a sieries iof steps so that
you can decide how you want the table in Jet trsformed into table in SQL
Server (for example, text column in Jet will be converted to NVarChar(255),
by default, but you can select VarChar(50) instead). However, if you use
"Copy", you do not have such choice. After copying, you definitely need to
go through the converted table and make sure the conversion is what you
really want. Oh, yes, whether you "copy" the table, or use Upsizing Wizard,
you need to re-set primary key for the table.

My point is converting JET database table to SQL Server database table is a
major change, since the two are different type of database system. Even with
DTS/Upsizing Wizard, it is still required to manually fix something to the
converted database/table. If you only convert databases of specific design,
surely you can write code with great control as to what exactly you want to.
However, why do you have to convert the same *.mdb(s) repeatedly so that is
worth writing code for that?
Click to expand...

Thanks for your reply Mr Yuan.

The reason for wanting to program in it, and do so repeatedly, is that we
have a front end application with a seperate backend which is in continual
development. The backend however is not unique, we are developing it in
BOTH access and sql server. Depending on their circumstances the users will
either have MS Access or SQL Server as their backend, and our app as their
frontend. We have absolutely no control over their choice of backend. So
what is happening is continual changes are being made to the backend
requirements (eg new tables, changes to the table structures etc) and we are
making them to the .mdb backend and then having to make them to the SQL
Server backend as well. I was hoping to automate this process somehow
whereby we make development changes to the backend .mdb and then via VBA we
can create the tables (its only tables we need) automatically.

To date we have been making the changes simply by clearing out the SQL
Server database via the .adp and copying by hand (copy and paste) the tables
and data over from the .mdb to the .adp, and then setting the primary keys
and default data values as this info is not copied over. It would be nice
to automate this process. If you have any info on how to do this, and info
on how to navigate .adp tables and set PK and defaults etc via VBA from a
..mdb this would be most helpful.

Thanks
Andrew
 
MacDermott said:
Are you just copying data from one database to another, or are the table
structures changing?
Click to expand...

Hello

We are copying both data and the table structures.

Regards
Andrew
 
Well, I'm not real knowledgeable about SQL Server, but here are my thoughts:
From your MDB, you should be able to create an ODBC connection to your
SQL Server database. I know there's schema information in a table or tables
there - sorry, I don't have the details. You should be able to read that
information to find out what tables don't match the ones in your mdb. (Or
skip this step if you just want to drop all the SQL Server tables and create
new ones.)
Then you can use DAO to analyze the tables in your mdb. Some of the SQL
Statements you might want to be executing against the ODBC connection might
include DROP TABLE, CREATE TABLE, CREATE FIELD, CREATE INDEX...

HTH
 
Back
Top