SQL scripts in Access

[alimcpill]

Does anyone know if it's possible to:

a) dump Access tables as SQL scripts (ie a CREATE TABLE and a load of INSERTS) <-this one is just out of interest

b) run scripts in access to create a number of tables and insert data? <this one is more important

reason: I may be able to get a MySQL db dump, and I'd like to recreate it in access. (I won't actually have access to the MySQL db, just a .sql file). I was thinking probably the best way to translate it to Access would be to run some sort of macro on the text to sort the syntax out, then run it as a script in access (like using the 'source' command in MySQL), but I'm not sure it will let me do this.

Anyone got any idea if this is possible?

TIA
Al

[jadedata]

You are going to save your self a ton on aspirin for that headache if you leave this go now and start building your tables in Access from the design screen.

You are going to be able to set properties that TSql won't cover and take advantage of the wizards to assist you along the way

There are tools out there that will convert MySQL to Access but they will cost you. This one:
http://www.itlocation.com/en/software/prd61640,,.htm
will cost you 70.00 USD

It was worth the cost for me to save the HOURS it would take to write all the sql.

Note: you will want to do some cleanup or tuning when the app it done.

[alimcpill]

OK thanks for the input, i'll have to consider how important it is before i actually buy anything to do this for me!

[xCav8r]

a) dump Access tables as SQL scripts (ie a CREATE TABLE and a load of INSERTS) <-this one is just out of interest

Yes, it?s possible, but I?ll echo jadedata?s sentiment here. Depending on the SQL supported by the destination database, it may be better to go with an off-the-shelf converter. If you do decide to go that route, I urge you to be careful and test things before you buy. I?ve tried out about a dozen utilities that convert between MySQL, PostSQL, SQL Server 2000, and Access. I?ve yet to find any that do what I want out of the box. There?s always a field or two (or many) that require different types.

If you want to go the manual route, VBAX?s own kpuls has a KB submission awaiting approval that will enumerate all the table names, field names, and data types. You?d have to modify it for the column types of the destination database. That?s the lion?s share of the dump. The INSERTs would be easy to generate with VBA.

b) run scripts in access to create a number of tables and insert data? <this one is more important

Why not keep it in MySQL? You can d/l the server for free, use your dump to recreate the database, and link to the tables remotely from Access.

reason: I may be able to get a MySQL db dump, and I'd like to recreate it in access. (I won't actually have access to the MySQL db, just a .sql file). I was thinking probably the best way to translate it to Access would be to run some sort of macro on the text to sort the syntax out, then run it as a script in access (like using the 'source' command in MySQL), but I'm not sure it will let me do this.

If you don?t like the suggestion I made above, then you?re going to have to do a lot of finding and replacing in the dump to make the SQL compliant with Access. That?s not extraordinarily difficult to do with VBA though.

[alimcpill]

Why not keep it in MySQL? You can d/l the server for free, use your dump to recreate the database, and link to the tables remotely from Access.


If you don?t like the suggestion I made above, then you?re going to have to do a lot of finding and replacing in the dump to make the SQL compliant with Access. That?s not extraordinarily difficult to do with VBA though.

Yeah I would love it if I could keep it in MySQL, but the problem I've got (and I wouldn't be surprised if there were some other VBA coder types who have similar problems) is that I don't work in IT, so if I request to set up a MySQL server, or even install for example Apache on my local machine to run a local server, IT will say "Why?" and shortly after that "No!!". I'm still looking at my options in regard to my original post, and as yet am undecided, but I do appreciate the input.

As an aside, this little anecdote sums our IT guys up. We have on our network both WinNT and WinXP machines. One day, one of my colleagues' start menu items didn't work properly. She was on an NT machine. She called the IT helpdesk and they sent someone up to look at it. Now anyone who has used these two OSs will know that in WinXP you can right-click any item in the start menu and get Properties to see exactly where it is pointing. This is not possible in WinNT, you have to right-click the start button and then explore or explore all users. The IT guy who came up, however, was ignorant to this, and started complaining loudly that 'the right mouse button doesn't work!". No joke, he actually replaced the mouse before we told him that in WinNT you can't just right click the start menu items. This may sound trivial to some of you, but it is typical of IT at my company. (Names withheld to protect the innocent (i.e. me!!))