Using Access...

[chrisjk]

hi there all

I know this should go in the Database forum, but it's like a graveyard!

Anyway, I am making a data app that uses an Access database to store data. Thing is, at some stage I will want to update my program (users might have suggestions) and as all of you know, if I simply replaced the old database with a new one, the data would be gone!

So does anyone have any suggestions as to how I might go about updating the database file with a new structure, but keeping the data? Is it possible to just copy the data over to a new blank database with the new structure?

By the way - end users may or may not have Access installed and I can't go to their machines to fiddle, it all has to be programatic. I hope to simply distribute an EXE that will do it all.

All help much appreciated...

Regards,

------------------
- Chris
chris.kilhams@btinternet.com
If it ain't broke - don't fix it :)

[jritchie]

Chris,

Do this all the time. The simple answer is if you are adding fields, and they are at the end of the record, take a copy of the existing database and import the data to the new database using Access's inbuilt data import options. Simply a matter of importing a table and then using cut and paste. Yes you can do it programmitically through DAO, ADO whatever. :)

[chrisjk]

Thanks for your help,

Could I get a little clarification...does this mean I can add fields AND tables etc to a database?

Also what built in data input options would those be?

Thanks,

kind regards,

------------------
- Chris
chris.kilhams@btinternet.com
If it ain't broke - don't fix it :)

[jritchie]

Yes you can add fields and tables to an existing database to your hearts content.

The import option is under File, Get External Data, Import...

Open the new improved database in Access.

Use the Import option to locate and import the old table you require, (if same name with suffix 1 to it)

Go to Data View, from Row 1 do a normal select of all data.

Copy to clipboard, (ctrl C)

Go to data view of new improved table and paste ( ctrl v)

Hope it helps :)

[chrisjk]

Sorry to keep going on, but surely that would require the end user to have Access, which they may not. How do I do it using ADO in VB (you mentioned above that you could)...?

Thanks for helping.

Regards,

------------------
- Chris
chris.kilhams@btinternet.com
If it ain't broke - don't fix it :)

[jritchie]

Oh ok you want the upgrade to happen on the user's PC. Ok you will have to write an app to map the old version and new versions and upgrade the new version with data from the old version. :(

[gravyboy]

I think Chris had grasped the 'I need an App' idea. What he was asking was has anyone got any programmatical knowledge of how to do this? . . . This interests me too.

------------------
Matt G
Either mattg@gravyboy.fsnet.co.uk] or matt.graveston@centrica.co.uk

[netSurfer]

It's pretty easy really. Since you will know both the old database as well as the new one, you already know all the differences. It's easy to add tables and fields. It's if you remove fields in a table that it would get difficult. Off the top of my head, all you would have to do is to create all the necessary tables that have changes to them. Then copy all the records (just grabbing the fields used) into the new tables from the old ones. Then delete the old tables. It would require renaming the new tables to the old table names. I'm at work right now so can't really hack out the code for you. If you want, I can hack out some at home and can email the code and database example. What version of VB and Access are you using? Feel free to email me. Keep in mind though that this may not be the only way, and depending on the number or tables and fields and records, it may be slow.

[This message has been edited by netSurfer (edited 01-10-2000).]

[Aaron Young]

You can Add/Remove Fields/Tables using the ALTER SQL Command in an Execute Statement, ie.

Dim oDB As Database
oDB = OpenDatabase("C:\Files\MyDb.mdb")
oDB.Execute "ALTER TABLE MyTable ADD COLUMN MyNewField Text(25)"
oDB.Close


------------------
Aaron Young
Analyst Programmer
aarony@redwingsoftware.com
ajyoung@pressenter.com

[chrisjk]

Hey - thanks guys

I'm sure I can get something happening with all this info..cheers!

kind regards,

------------------
- Chris
chris.kilhams@btinternet.com
If it ain't broke - don't fix it :)