MySQL file in Access

[JustJim]

Hi all,

I have been given a file of a MySQL database which I'm supposed to familiarise my self with and in a couple of weeks start writing queries and reports to generate a price book.

The file has the extension .sql and it is full of lovely looking SQL (example below) and a lot of data, but I can only open it as a text file. A little bit of research tells me that Access can open .sql files but I can't seem to get my Acess 2007 to do so. Any hints?

/*
SQLyog Enterprise - MySQL GUI v6.07
Host - 3.23.58-log : Database - gold
*********************************************************************
Server version : 3.23.58-log
*/
 
 
create database if not exists `gold`;
 
USE `gold`;
 
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
 
/*Table structure for table `category` */
 
DROP TABLE IF EXISTS `category`;
 
CREATE TABLE `category` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(60) NOT NULL default '',
  `url` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;
 
/*Data for the table `category` */
 
insert  into `category`(`id`,`name`,`url`) values (5,'Small Trailing Sprayers','trailing'),(6,'Large Trailing Sprayers','trailing'),(7,'Rowcrop Trailing Sprayers','trailing'),(8,'Self Propelled Sprayers','self'),(9,'Truck Mount Sprayers','self'),(10,'Ground Gliders','trailing'),(11,'Pasture 3 Point Linkage Sprayers','3pl'),(12,'Rowcrop 3 Point Linkage Sprayers','3pl'),(13,'Director 3 Point Linkage Sprayers','vine'),(14,'Director Trailing Sprayers','vine'),(15,'Weedicide Sprayers','vine');
 
/*Table structure for table `content` */
 
DROP TABLE IF EXISTS `content`;
 
CREATE TABLE `content` (
  `id` text,
  `content` text,
  `link_text` text NOT NULL,
  `link_url` text NOT NULL,
  `link_group` int(11) NOT NULL default '0',
  `header_group` int(11) NOT NULL default '0',
  `menu_order` int(11) NOT NULL default '0',
  `last_edit` text NOT NULL,
  `matatags` text,
  `draft` text,
  `restricted` text NOT NULL,
  `folder` text NOT NULL,
  `drop_down` text NOT NULL
) TYPE=MyISAM;
 
/*Data for the table `content` */

etc, etc

[Delerna]

I don't know about access opening .sql files, but they may not run in access ecen if you could. 30% of your pasted code wont.
Also .sql are in SQL Sever/MySQL syntax and although access sytax is identical on the main there are slight differences

You could try creating the table manually in access with the same field names and datatypes. Then you could
copy the queries from the .sql file
create a new query in acces
then while in design mode of the query click the view menu and select the sql option and past the query in there. You may need to make a few modifications to get it to work due to the sytactical differences (probably not though)

Someone else may know an answer to using the .sql directly.

Have you tried the tutorials and samples in the help file. You would probably be better off starting there. If you have any specific difficulties ask the question here

[Delerna]

Oh, I forgot
Alternatively you could run the .sql file in MySQL and then import the tables into access.

You are meant to familiarise yourself with MySQL and Access in a couple of weeks. WOW.

[JustJim]

You are meant to familiarise yourself with MySQL and Access in a couple of weeks. WOW.

No, no it's not that scary. I'm very familiar with Access and SQL, I just don't have MySQL at home. If I could only see the relationships visually and see the meta-data then I would be right.

Actually I guess I can if I just sit down and read through what is effectifly a text file. I was just hoping it would be easier.

Any further help would be appreciated.

Jim

[NeoPa]

You can copy and paste any SQL from your .SQL files into the SQL view of a new query in Access. The ANSI standard used in your Access 2K3 or 2K7 database will determine if it interprets it all correctly of course (ANSI Standards in String Comparisons). You should also remember that different queries separated by GO commands should not be lumped together in one query. Lastly, the record sources should all be available to the Access db before you expect to get much of use in a QBE display.

[JustJim]

You can copy and paste any SQL from your .SQL files into the SQL view of a new query in Access. The ANSI standard used in your Access 2K3 or 2K7 database will determine if it interprets it all correctly of course (ANSI Standards in String Comparisons). You should also remember that different queries separated by GO commands should not be lumped together in one query. Lastly, the record sources should all be available to the Access db before you expect to get much of use in a QBE display.

Yes, the trick is to get the record sources read from the .sql file. Not having any luck with that.

Jim

[NeoPa]

Are they local tables?
Do they need to be linked first?

I'm not sure exactly where your problem lies (so am not sure if I can even help at this stage).

[sierra7]

Hi Jim
I'm hooking into this so I can follow your progress. I have the books and I've downloaded MySQL to demo upsizing from Access, but can't find the time.

Your sample .sql files are obviously 'procedures' for setting up a database. When I did Oracle some years ago (My God it's over 23 years!) we used similar command files to create the database, set up users and permissions etc. I'm out of the habit being spoilt by the Access GUI.

I suggest you download MySQL (it's free), fire it up and see what happens when it reads your files. Get some 'hands on'.

The syntax is similar but different from Access SQL. What would you do with all those single quotes? Some can go to square bracket but how do you decide left or right?

I shall watch this space with interest!
Best of luck

NeoPa; is there a way of monitoring a thread and recieving notification without joining it, please?

S7

[NeoPa]

There should be S7.
It was there but has been lost after a recent update. I think everybody liked it so I expect to see it back again sometime soon. I assume you mean without adding a post?
Before the "Subscribe" button was introduced though, it was common practice simply to add a post consisting of "Subscribing" (although the 20 char minimun applies of course).

[Delerna]

Yes, the trick is to get the record sources read from the .sql file. Not having any luck with that.

Jim

You could copy this

INSERT  INTO `category`(`id`,`name`,`url`) VALUES (5,'Small Trailing Sprayers','trailing'),(6,'Large Trailing Sprayers','trailing'),(7,'Rowcrop Trailing Sprayers','trailing'),(8,'Self Propelled Sprayers','self'),(9,'Truck Mount Sprayers','self'),(10,'Ground Gliders','trailing'),(11,'Pasture 3 Point Linkage Sprayers','3pl'),(12,'Rowcrop 3 Point Linkage Sprayers','3pl'),(13,'Director 3 Point Linkage Sprayers','vine'),(14,'Director Trailing Sprayers','vine'),(15,'Weedicide Sprayers','vine');
 

and make a csv file like this

id,name,url
5,Small Trailing Sprayers,trailing
6,Large Trailing Sprayers,trailing
7,Rowcrop Trailing Sprayers,trailing
8,Self Propelled Sprayers,self
9,Truck Mount Sprayers,self
10,Ground Gliders,trailing
11,Pasture 3 Point Linkage Sprayers,3pl
12,Rowcrop 3 Point Linkage Sprayers,3pl
13,Director 3 Point Linkage Sprayers,vine
14,Director Trailing Sprayers,vine
15,Weedicide Sprayers,vine

Import that into excel and then import the worksheet from excel into access as a table
Then you could re-name the table to 'category'


Or maybe access can import the csv directly, I don't know I have never tried

[JustJim]

Hi Jim
I'm hooking into this so I can follow your progress. I have the books and I've downloaded MySQL to demo upsizing from Access, but can't find the time.

Your sample .sql files are obviously 'procedures' for setting up a database. When I did Oracle some years ago (My God it's over 23 years!) we used similar command files to create the database, set up users and permissions etc. I'm out of the habit being spoilt by the Access GUI.

I suggest you download MySQL (it's free), fire it up and see what happens when it reads your files. Get some 'hands on'.

The syntax is similar but different from Access SQL. What would you do with all those single quotes? Some can go to square bracket but how do you decide left or right?

I shall watch this space with interest!
Best of luck

NeoPa; is there a way of monitoring a thread and recieving notification without joining it, please?

S7

I agree with everything (that's the kind of guy I am!). I tried to download MySQL, but I ended up with some sort of server service program. Where did you find the actual MySQL program?

Thanks for your interest, sorry I've been so slow, but I've been away on and off.

Jim

[JustJim]

You could copy this

INSERT  INTO `category`(`id`,`name`,`url`) VALUES (5,'Small Trailing Sprayers','trailing'),(6,'Large Trailing Sprayers','trailing'),(7,'Rowcrop Trailing Sprayers','trailing'),(8,'Self Propelled Sprayers','self'),(9,'Truck Mount Sprayers','self'),(10,'Ground Gliders','trailing'),(11,'Pasture 3 Point Linkage Sprayers','3pl'),(12,'Rowcrop 3 Point Linkage Sprayers','3pl'),(13,'Director 3 Point Linkage Sprayers','vine'),(14,'Director Trailing Sprayers','vine'),(15,'Weedicide Sprayers','vine');
 

and make a csv file like this

id,name,url
5,Small Trailing Sprayers,trailing
6,Large Trailing Sprayers,trailing
7,Rowcrop Trailing Sprayers,trailing
8,Self Propelled Sprayers,self
9,Truck Mount Sprayers,self
10,Ground Gliders,trailing
11,Pasture 3 Point Linkage Sprayers,3pl
12,Rowcrop 3 Point Linkage Sprayers,3pl
13,Director 3 Point Linkage Sprayers,vine
14,Director Trailing Sprayers,vine
15,Weedicide Sprayers,vine

Import that into excel and then import the worksheet from excel into access as a table
Then you could re-name the table to 'category'


Or maybe access can import the csv directly, I don't know I have never tried

Could do... It's actually the relationships between the tables, which has whos foreign keys etc, that I'm interested. As I said, I could do that by opening the .sql file as a text file and reading it with paper and pencil handy. Would be easy to miss something if the naming conventions aren't good though.

Sorry to be so slow, I've been away for a while.

Jim

[sierra7]

I agree with everything (that's the kind of guy I am!). I tried to download MySQL, but I ended up with some sort of server service program. Where did you find the actual MySQL program?

Thanks for your interest, sorry I've been so slow, but I've been away on and off.

Jim

Hi Jim
I must admit that I bought a SAMS "Teach Yourself PHP, MySQL and Apache in 24 Hours" book (sound like what you need!) and it had a CD with the software (version 4)

I've just had a look at the MySQL website and I guess you would need "MySQL Community Serrver Version 5 for Windows". I eventually navigated to;-
http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-essential-5.1.22-rc-win32.msi/from/pick
where you could pick a mirror site depending on your location.

The are many other download for Manuals, Installation instructions etc. and options for more operating systems that I have heard of, so I guess it does get confusing.

Best of luck

NeoPa: Thanks. I thought I had seen a Subscribe button but had managed to change my default settings by mistake.
I also thought you could view the responses in reverse order, which would have been very useful in dialogue with a certain OldBirdman recently! My scroll button is worn out.

S7

[NeoPa]

To view in reverse order simply :

1. Click on the Control Panel link at the top of any page.
2. Look under Settings and Options down the left hand side and click on Edit Options.
3. In the Thread display Options box there is an item Thread display Mode. You need to go in there and select "Linear - Newest First".

[sierra7]

NeoPa
Thanks for taking time to write comprehensive instructions.
S7

[NeoPa]

Not a problem.
I'd even do it for members who aren't as active and helpful as you are Sierra ;)

PS. Remember the Home & End keyboard keys will work for you too, to get to each end quickly.

[JustJim]

Hi Jim
I must admit that I bought a SAMS "Teach Yourself PHP, MySQL and Apache in 24 Hours" book (sound like what you need!) and it had a CD with the software (version 4)

I've just had a look at the MySQL website and I guess you would need "MySQL Community Serrver Version 5 for Windows". I eventually navigated to;-
http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-essential-5.1.22-rc-win32.msi/from/pick
where you could pick a mirror site depending on your location.

The are many other download for Manuals, Installation instructions etc. and options for more operating systems that I have heard of, so I guess it does get confusing.

Best of luck

NeoPa: Thanks. I thought I had seen a Subscribe button but had managed to change my default settings by mistake.
I also thought you could view the responses in reverse order, which would have been very useful in dialogue with a certain OldBirdman recently! My scroll button is worn out.

S7

That looks exactly what I'm looking for. Thanks for your help.

Jim