how to add defaults in MySQL from Access data transfer

[janetopps]

I used a database transfer utility (Bullzip) to move data from Access to MySQL Asp pages,

I couldn't transfer the data if i did not opt to leave the default values, therefore the default values didn't get transfered.

I checked the access database and some of the fields have default values, ie, current time to go in automatically.

Obviously, these being absent in MySQL are causing problems as they show NULL

I use MYSQL query browser, . from mysql.com, the GNU one,

Can i add the defaults using this? I also note it has a migration utility, but im not sure what i can do with it , since ive already transferred the data. I only want to add defaults.

Does anyone know a simple way for me to do it? I do have a copy of the old Access database on my desktop, so should i just manually do it?

If anyone has done anything like this, pls offer some tips. Many thanks in advance.

[janetopps]

i'll explain in a little more detail, so it's all clear and no misunderstandin g.

I opened the old access database, right clicked and chose "design view".


This brought up some data, and i'll show you a few relevant lines from the old ACCESS database. Here are 4 columns in this table, i had to colour them differently, the bold is the top column, and their fields are in the same colour.


[COLOR="Green"]Field Name[/COLOR] [COLOR="darkoran ge"]Data type [/COLOR] [COLOR="purple"]Description[/COLOR] [COLOR=Red]Default Value[/COLOR]

[COLOR="green"]fldPosted [/COLOR] [COLOR="darkoran ge"]Date/Time [/COLOR][COLOR="purple"]Article is posted since[/COLOR]

[COLOR="green"]fldUpdated [/COLOR] [COLOR="darkoran ge"]Date/Time [/COLOR] [COLOR="purple"]Last updated[/COLOR]

[COLOR="green"]fldExpires[/COLOR] [COLOR="darkoran ge"]Date/Time [/COLOR]

[COLOR="green"]fldCREATED [/COLOR] [COLOR="darkoran ge"]Date/Time[/COLOR] [COLOR=Red]Now()[/COLOR]


Now to the new database MYSQL


I use MySqL query browser Version 1.2.13

This particular software came with various database scripts to run against a database, with a new installation.

I have an MySQL script that can be run against the MySQL database. But i think this only works if you start new, and not with a trasfer of data as im doing. I'm transferring an Access database records to MySQL


nm_tbl_news is one table in the database, the one that contains the article details when i add them. This is what the mysql script that you're supposed to run on the database when you install it says about this table

[COLOR="darkred"]CREATE TABLE `nm_tbl_news` (
`ID` int(10) NOT NULL auto_increment,
`fldTITLE` varchar(85) default NULL,
`fldCONTENT` longtext,
`fldSUMMARY` varchar(255) default NULL,
`fldACTIVE` int(10) default '1',
`fldAID` int(10) default NULL,
`fldPOSTED` datetime default NULL,
`fldUPDATED` datetime default NULL,
`fldEXPIRES` datetime default NULL,
`fldCREATED` timestamp NULL default CURRENT_TIMESTA MP,
`fldVIEWS` int(10) default '0',
`fldIMAGE` varchar(255) default NULL,
`fldVOTES` int(10) default '0',
`fldVALUE` int(10) default '0',
`fldALLOW_COMME NTS` int(10) default '0',
`fldALLOW_VOTIN G` int(10) default '0',
`fldUPDATE` datetime default NULL,
`fldHIGHLIGHT` int(10) default '0',[/COLOR]

As an experment i just ran the complete script which includes the part above, against the MySQL database and it deleted all the transferred data. However, i did see default values in the tables, when i checked "edit table" in the query browser. But the records showed 0, which means they got deleted.


Since i now had the tables and default values, but no data, that was no use to me. I then re-transferred the data from the old Access database, using Bullzip, and as i explained earlier i can't transfer the default values, as some are not recognised, so i unchecked that button. The data transferred OK, overwriting what was already there, but no default values obviously. The 3 new records i'd added to the new mysql database since the transfer obviously got deleted, so i'm back with MYSQL, with the old data.

Before i experiment and add new records, i think i'd better add some default values? In particluar the fldCreated, as this one is causing a problem on a page that relies on it.

So I guess i need to add them manually? If you look at the script above it says [COLOR="darkred"]"CURRENT-TIMESTAMP" [/COLOR]as the default value.

Just so there is no confusion, this value is supposed to remain unchanged once added, it is not the current time that it should display, but the date that the article was actually added to the database.



The record that is causing problems in particular is the fldCREATED one. The field, shows the date that the article was actually created and added to the database. It is different to the published date, because the software gives you the ability to add an article now, and set the date to appear in public for let's say, next week, as an example. So this date fldCreated remains unchanged, even though the other dates can be altered automatically, ie. if you change the published date, or the update article date, expiry date etc.

Should i add this manually in the "Edit table" facility or what? and what should i put?


Would the solution be for me to open both databases on my desktop and manually add the defaults in the MySQL query browser? I'd like to sort out the fldCREATED first, but i think i'd better do the other ones too.

Thanks in advance