adding defaults in MySQL from Access data transfer


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: adding defaults in MySQL from Access data transfer

  1. #1
    Join Date
    Apr 2007
    Posts
    42

    adding defaults in MySQL from Access data transfer

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

    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.

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    Default values in Access are simply a way to calculate a value to insert into the field that has no value when a new record is inserted. Once the record exists the value is there in the field, so when you do the transfer all of the values would go.

    Now if you are trying to add the same default value process to your MySQL table you will have to set that up in MySQL, probably with the use of triggers or something similar.

  3. #3
    Join Date
    Apr 2007
    Posts
    42
    Ron thanks, and i'll explain in a little more detail, so it's all clear and no misunderstanding. I'd appreciate it if you or someone else could tell me what to try where exactly for the field below, i'm not html literate.


    anyway......

    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.
    Field Name Data type Description Default Value

    fldPosted Date/Time Article is posted since

    fldUpdated Date/Time Last updated

    fldExpires Date/Time

    fldCREATED Date/Time Now()

    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

    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_TIMESTAMP,
    `fldVIEWS` int(10) default '0',
    `fldIMAGE` varchar(255) default NULL,
    `fldVOTES` int(10) default '0',
    `fldVALUE` int(10) default '0',
    `fldALLOW_COMMENTS` int(10) default '0',
    `fldALLOW_VOTING` int(10) default '0',
    `fldUPDATE` datetime default NULL,
    `fldHIGHLIGHT` int(10) default '0',


    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 "CURRENT-TIMESTAMP" 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
    Last edited by karen987; 11-19-2008 at 06:32 AM.

  4. #4
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,261
    My guess would be that the fldCREATED is the wrong data type.
    You have it set to timestamp when it should be datetime.
    Also you may need to change the default as well
    If CURRENT-TIMESTAMP is only the current time then you will need one that
    gives you both Date And Time.

    The Now() function in Access returns both date and time. You need one in MySQL that
    does the same. Is there a CURRENT-DATETIME ?

Similar Threads

  1. trsafering access record in sql data base...
    By luca90 in forum Database
    Replies: 1
    Last Post: 01-14-2007, 07:03 AM
  2. Why I can't save data in data base?
    By Kurt in forum Java
    Replies: 7
    Last Post: 10-08-2006, 08:59 AM
  3. Replies: 0
    Last Post: 08-13-2003, 01:56 PM
  4. PROBLEMS TO ACCESS INFORMIX DATA FROM VB
    By RADIAZMTZ in forum Database
    Replies: 0
    Last Post: 04-24-2002, 03:17 PM
  5. Access 97 to 2000 Conversion Error
    By Michelle in forum VB Classic
    Replies: 8
    Last Post: 04-19-2002, 05:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center