exporting to mysql from access with primary keys intact?

Is there any way to export my tables from access and keep the primary keys intact At the moment I use the DSN driver to link the tables and feed them into mysql but I lose the primary keys.

Posted On: Wednesday 24th of October 2012 09:22:04 PM Total Views:  243
View Complete with Replies

Related Messages:

Options exporting few Excel columns to MySQL   (136 Views)
I have to insert data into MySQL to be used afterwards with a webapplication. For convenience sake, as it's a lot of data, the data is first taken from paper sheets and inserted in an Excel worksheet. The data in some columns need to be imported into some existing table of a MySQL database. I am still working on a good layout for the excel sheet, but it will mostlikely start with a heading to state what product, client, etc. the following data belongs to. It's always a new product, client, etc. so that heading can be used to insert a new product_id to which the data will be linked. In other words 2 existing linked tables are used. After that there's few columns with data and a clear column-header stating what data follows. Question is... how to make it easy on myself (and others) to import it into MySQL I've searched this forum already and some solutions presented themselves and I guess that some people got some renewed experience with it, so... what's your experience with it Especially the final option is something I am pondering about as it sounds cool, but maybe a bit TOO much. Most important is that the Excel data is processed without problems. 1). I could use XLreader in combination with PHP, so an XLS file can be uploaded by means of a webform; it's converted to CVS and its data can be parsed and processed by PHP before insertion into the database. Question is: how failproof is it Anybody had some nasty pitfalls to solve with a CVS file After all, it's just ";" delimited text. 2). I could use XML Excel worksheets instead of XLS files since they can be used/reused from the start. Like with (1) it would be processed, but the XML might give me some better control and error-management in case some data seems screwed. Anybody has had experience with this and experienced some things to look out for 3). Final option I thought of sounds really cool, but NO idea what problems it may give me. An XLS template on the webserver; people open it inside the IE browser (intranet IE only application); insert all the data they got into the template; press a button (hooray for VBA); by means of myODBC the data is inserted into the database. This last option sounds very integrated, no remaining Excel sheet, less time-consuming as one doesn't have to process it by means of a form. HOWEVER...( )... there's no PHP processing and so far I got little to nihil experience in VBA. The header needs to be put in e.g. product table while the following data needs to be verified and put into the linked table. Am I asking for major pitfalls and pain here to get it to work this way JUST for the sake of "ease" I want to check my options... Anybody got experience with this last option Any good tutorial on MySQL in combination with Excel/VBA/ODBC
Import and exporting database   (162 Views)
Hi im trying to export and import and database for a joomla site. I have exported it fine but when i import it it comes up with this SQL query: CREATE TABLE IF NOT EXISTS `jml_usergroups` ( `id` int( 10 ) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary Key', `parent_id` int( 10 ) unsigned NOT NULL DEFAULT '0' COMMENT 'Adjacency List Reference Id', `lft` int( 11 ) NOT NULL DEFAULT '0' COMMENT 'Nested set lft.', `rgt` int( 11 ) NOT NULL DEFAULT '0' COMMENT 'Nested set rgt.', `title` varchar( 100 ) NOT NULL DEFAULT '', PRIMARY KEY ( `id` ) , UNIQUE KEY `idx_usergroup_parent_title_lookup` ( `parent_id` , `title` ) , KEY `idx_usergroup_title_lookup` ( `title` ) , KEY `idx_usergroup_adjacency_lookup` ( `parent_id` ) , KEY `idx_usergroup_nested_set_lookup` ( `lft` , `rgt` ) USING BTREE ) ENGINE = MYISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT =13; MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=13' at line 11 any help would be great
Two table mysql query.   (219 Views)
Hi Guys, I've currently got this mysql query: Code: $queryA = "SELECT id, active, image, title, text, poster, date_format(date,'%a %D %b %Y %H:%i') AS datef FROM site_reviews WHERE active = '2' ORDER BY date DESC LIMIT 5"; $resultA = @mysql_query($queryA) or die(mysql_error()); while ($rowA = mysql_fetch_assoc($resultA)) { ..... } But in the query above i'd also like to perform the same search on table 'site_articles' (which has exactly the same layout as site_reviews). The end result should be limited to 5 results from site_reviews and 2 results from site_articles with everything still arranged in date order. Is this possible
Sql server 2005 to mysql   (206 Views)
Is there a way to migrate sql server 2005 database with views and storedprocedure to mysql
Generating a mysql table from a csv   (128 Views)
I do loads of data work involving importing and manipulating data and i'm constantly having to build tables to import data into and its a right pain. Are there no tools which will look at a csv file and based on what it finds build a table for you before importing the data
Connecting to mysql   (207 Views)
, i have just installed mysql on my windows 2003 server and nwo i have a question, 1: after installing it. how do i connect to it to make tables etc 2: i use IIS 6 and i have my webroot folder on q:/webroot and wonder if i can set the mysql database to be in q:/webroot/database and how i would do that sorry im a little new to doing this!
/tmp/mysql.sock IN PLAIN ENGLISH PLEASE!   (124 Views)
Guys I only found out SQL exists yesterday, and I still dont understand what it is. But I have followed instructions on a self install script word document. However, logging into my domain name which is on a sharred hosting Linux server(yes I painfully found this out) it gives me, I say it I mean opening up the .php extention page which I uploaded onto using FTP. I get the following error: /tmp/mysql.sock And thats that. I have no idea how to fix this, and after googling in up, all I get is ideas of changing the root file ect ect. Whats a root file I dont host the site myself, its on a sharred hosting site. Ok if anyoner here can help me you will be nominated for an oscar award as far as I am concerned.
Losing connection to mysql database during query   (241 Views)
i have a cronjob which runs every 24 hours at 12 am, which is aphp script which does a mysql query. and on an event where the script fails to do what it was supposed to do, it emails me with the mysql_error() so i can check it out. apparently it failed initialy connecting to the mysql database (which is located on the same machine as this cronjob php scrpit) this is the exact error.. connection to database failed.. mysql_error: Lost connection to MySQL server during query the connection to database failed part is something i writtin in so i know which mysql_query failed when the email was sent. looknig into the script is what happened is the mysql_connect() statement failed (the parameters i sent to it are the correct ones) right now this script is working as is. just at that one time it woudlnt connect. how could i figure out what casued this. and what are some preventitive actions i could take
Can't get stable copies with mysqldump???   (164 Views)
I scheduled a cron job for mysqldump to run every day at midnight. But looks like many of the backups are partial, not a complete backup of the origianl db(when I recovered the backups, many of them only got the first table with less rows that there should be). Can anybody tell me where the problem resides
Using mysql in linux   (131 Views)
I have installed MySQl in linux and its working properly. I wrote an c++ program to connect to mysql database, which is capable of retrieving, deleting the values from database table. The problem i am facing is i am unable to insert values into the table at runtime. Can anyone please help me as to how to write the query Thankyou in advance.
Just installed mysql - cannot log in to change password   (278 Views)
I am borderline about to throw my computer out the window....I have installed, removed, installed again, removed again mysql 5.0 on my brand new Fedora 5 core with little success. I am logged in as root, and can get mysqld to start, however when I attempt to change my password by the following: mysql -u root I get the following message: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) How can I be denied if I just installed the damm thing!! I do not get this at all. Any help would be greatly appreciated. I am logged in as root, I'm have just installed mysql - what do I do , Also check this thread if you haven't already. Another user has just gone through what sounds like a similar set of issues, you might find some clues there. , files ending .gz are usually a type of zip file. Sounds like it may have been downloaded but not unzipped. Can you describe exactly what you did to install mysql , Quote: Originally Posted by askjoe yum install mysql This is the exact command I used Are you certain you didn't install the mysql client alone Unless I'm mistaken, fedora packages mysql client and server in seperate packages. Try 'yum install mysql-server'. , Hmm. It's been awhile since I ran either Redhat or Fedora. What is the output of this command: Code: chkconfig --list | grep 'mysql' , Ok, so it indicates that it is installed and set to run at init level 5 (basic multiuser / X runlevel), so that's fine as long as you're logged in to X. I might change it to run in both '3' and '5', but it should be ok for now. Ok, how about trying these commands (as root or superuser) Code: /etc/init.d/mysqld restart and Code: netstat -autv | grep 'mysql' and finally Code: grep 'localhost' /etc/hosts That last command there, I'm curious if you're simply missing a 'localhost' entry in your /etc/hosts file. , Hmm, well as they say, "that's a good one". Everything seems to indicate that the server is installed, running and ready to accept connections, from any IP, on the default port. Have you run down the list of 'causes of access denied errors' I'm not certain where the log might be, have you checked /var/log Which version of mysql-server is installed Do you have the latest updates Have you had any success logging in as the MySQL 'root' user Have you tried simply logging in as a guest user, e.g. simply calling the `mysql` command without specifying a user with the -u switch Tried logging in as 'root' or the guest user with your 'other' hostname, e.g. the machine name Code: mysql -u root -h xyz Where 'xyz' is your hostname.
Fedora Core MySql - mysql.sock Error 2002   (307 Views)
I have installed server,client and devel of mysql-4.1.11-2 on Linux Fedora. I am not able to start the mysql. The erroe I am getting is ERROR 2002 (HY000) : Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' Now this mysql.sock is present in /var/lib/mysql/ directory it is present in /tmp Even I tried /etc/rc.d/initd/mysqld -restart then sql stop and sql start both FAILED Can any body tell me what is the problem here
What Linux to install for mysql   (237 Views)
I am creating a dedicated DB server running mysql. What is the best linux to install for this Also the db server will have its own ip is there any other additional security measures that should be taken thanks , VectorLinux may be a good choice. It's a faster version of slackware, but it's based on slackware, so you should get good stability. Haven't played with it very much yet. SoL (Server Optimized Linux) is another one to look at. It's designed to be just a server. OpenBSD is the most secure solution, however I don't know what the performance of MySQL is on a BSD platform compared to a Linux platform.
LIFO - last in first out & using filesort problems in mysql   (245 Views)
LIFO - last in first out & using filesort problems in mysql * How to improve table structure & query better, 'Using filesort' not happend mysql 4.0.21 table ad( siteid int primary key sitetitle varchar(30) sitedescription text sitedate datetime ) index sitedate data: select * from ad result: siteid , sitetitle , sitedescription , sitedate 1 , keyboard, 3 items , 2006-05-16 17:07:46 2 , motherboard , nothing , 2006-05-17 17:07:02 3 , mouse , stock , 2006-05-17 17:07:49 4 , computer , expired , 2006-05-18 07:01:46 . . . if table record are 2000000 then explain select * from ad order by sitedate then 'Using filesort' happend so the query very slow (20 seconds with 30 connection) * How to improve table structure & query better, 'Using filesort' not happend * How to improve table structure & query better, then query display as LIFO - last in first out without, using order by * How to improve table structure & query for LIFO whenever update to sitedate field exp : select * from ad where siteid in (3,4) the result: siteid , sitetitle , sitedescription, sitedate 4 , computer , expired , 2006-05-18 07:01:46 3 , mouse , stock , 2006-05-17 17:07:49 * if I do: update ad set sitedate = '2006-05-19 07:01:46' where siteid=3 result exp : select * from ad where siteid in (4,3) the result: siteid , sitetitle , sitedescription , sitedate 3 , mouse , stock , 2006-05-19 17:07:49 4 , computer , expired , 2006-05-18 07:01:46 Thank you all people
How can I still keep mysql querying once it hits an error?   (436 Views)
I have a query: Quote: mysql_query("insert into $dbname.brands (brands) select distinct brands from $dbname.prodse_$mid where app=1 limit $views, $records"); On the insert table there is a unique index so that I only get one of each value. I have also used distinct on the select part of the query so that it doesn't throw up any errors when it can't insert a value due to it being a duplicate. Everything works fine apart from I need to keep these values that I have already inserted and only some of them get deleted that aren't used anymore. I have to update this table every day that has the inserts in, but when I try this it does not work, because it just throws up and error as it is trying to insert duplicates where it can't because I am using unique index. How can I stop the query from stopping once it hits a duplicate and then fails. How can I keep it going on to the next row of the select part of the query.
Securing new mysql installation   (267 Views)
I just installed mysql 4.x on Kubuntu 5.10 via apt-get. I want to secure the account as per the instructions found at: I apparantly have these users: mysql> select Host, User FROM mysql.user; +-----------+------------------+ | Host | User | +-----------+------------------+ | ety | root | | localhost | debian-sys-maint | | localhost | root | +-----------+------------------+ 3 rows in set (0.11 sec) So I proceed tto the first step in the guide, but I get this error: mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('my_password_here'); ERROR 1133: Can't find any matching row in the user table What is the proper way to continue Should I use these three commands: mysql> SET PASSWORD FOR 'root'@'ety' = PASSWORD('my_password_here'); mysql> SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('my_password_here'); mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('my_password_here'); Thank you in advance.
Mysqldump & mysqlimport question   (381 Views)
hi I have done: mysqldump -u root -p --all-databases > file.sql which dumped arounf 80megs now, I am struggling to import it back on another server what shoudl i use mysqimport what syntax cheers
Problems logging into mysql via shell script   (196 Views)
Greetings, I am trying to come up with an import script to automate nightly updates of a remote mysql database. I can't figure out how to make the login command to the password as an argument and not prompt me for it. It gives me a password error even though the password is correct (believe me, ive checked a hundred times). I just don't think im sending it correctly Here is the whole script: mysql -h database -u username --password=password use databasename truncate table tablename LOAD DATA LOCAL INFILE '/path/to/csv/file.csv' INTO TABLE `tablename` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'; If I try to log in manually I have no problems mysql -h database -u username -p It then prompts me to enter my password, I enter it, and get in without a problem. If there is not a way to send the password in the script, is there a way to have it entered automatically in the prompt
mysql datetime columns vs integer columns using unixtimestamps   (232 Views)
Up until now, I've been storing my times in an integer field as unix timestamps. I'm thinking about making the switch to mysql's date/time column types instead but I have a question. I do a lot of comparing with dates and was wondering if using mysql's date/time column types will slow down my queries since it has to convert the column to an "internal long integer" to do a comparison rather than not just comparing straight integers with a unix timestamp in an integer column. source: Quote: Originally Posted by When you compare a DATE, TIME, DATETIME, or TIMESTAMP to a constant string with the , or BETWEEN operators, MySQL normally converts the string to an internal long integer for faster comparision (and also for a bit more relaxed string checking). I'm hoping that I'm just being paranoid and the actual performance difference is neglible. Please discuss.
Are too many mysql privileges/users a bad thing?   (216 Views)
I was wondering what the suggested practice is when it comes to mysql privileges/users I have a bunch of individual apps that I currently have a dedicated username per app. I was reading that too many usernames/privileges may actually be a bad thing and was wondering what the best practice would be Having individual passwords I would think would be best for security as in case there is an issue with the code, peoeple could not exploit the other databases. Is it common practice to create 1 username/password for all your databases