Anyone on here good with databases??

We have had an online database created to manage competition entries but it’s proving inefficient when managing the scoring of said entries so i have asked the software developer to provide a data dump that can be imported into Access. They have come back to say that this is not possible because of the version on MySQL that they use (thinking that they may just be saying this in fear that in doing so they’ll be thinking that less $$ will be coming their way) and have instead sent through a .sql file that can not be read by Access. I can open it in notepad and see all the info and sql coding so it does appear to be the full database but just completely unusable.

There are numerous utilities on the web for converting SQL to Access but I haven’t found one yet that does not require the SQL database to be hosted somewhere (can’t connect directly to the software co’s server due to NHS firewall & IT politics!!). Does anyone know of such utility where I can convert a .sql file without the database being hosted, or is there a simple way to host the SQL database locally so that I can connect a utility to convert?

I figured there’s a few IT bods on here so worth a shot…
Any help appreciate!
Carl

Can you ask them to give you all the tables as text files then you can import them into access that way?

so get each table as .csv? would that not require a lot of rebuilding the database once i have imported them?

not trying to be funny but how is sql proving ‘less efficient’ than Access – you might be better focusing on that?

As above… I suspect what you need is an easy way of looking at the data. Tell the developer the problem and I’m sure they can sort it

You can import .csv (comma seperated value) files into access and recreate the db that way – BUT if the db is massive Access will not be able to handle it.

The original Database unless built, maintained and queried by low end of the spectrum mammals (possums possibly) should be able to do everything you ever wanted and more.

as above – you’re barking up the wrong tree if you think Access is the way forward……

You shouldn’t need to do this. The SQL db should perform way better than any equivalent in Access.

I suspect that the current implementation is poorly coded.

the problem is that the original spec for the database was never really thought through properly (by my predecssor) when the original draft was given to the developer. the database works great in terms of collecting and viewing the data it just lacks appropriate functionality to manage it how it needs to be managed, if that makes any sense.

obviously i have spoken with the developer about this and they have quoted thousands of pounds to develop it further but still not sure that it would then perform exactly how i want it to. the idea of being able to dump it all into Access will enable me to properly test and manipulate the data to figure out exactly what i need doing before spending lots more money.

i probably gave you all too much info and you’re now reading too deeply into things, honestly we’ve been thinking of numerous ways to get around this – basically just after some answers on the last paragraph of my original post:

There are numerous utilities on the web for converting SQL to Access but I haven’t found one yet that does not require the SQL database to be hosted somewhere (can’t connect directly to the software co’s server due to NHS firewall & IT politics!!). Does anyone know of such utility where I can convert a .sql file without the database being hosted, or is there a simple way to host the SQL database locally so that I can connect a utility to convert?

carl

Moving from SQL to Access because SQL is “inefficient” is like buying a new car because the old one is too cold, when what you should do is think about closing the windows. people move FROM Access to SQL for that reason, not the other way round. As above, get your developer to fix the problem.

peachos
I understand your problem and if you don’t know mysql but are happy with the Access UI I can see why you want to do this.

It seems a bit mad, but one option is to install mysql on your local PC. Get the developer to dump the database with:-

mysqldump -u root -p <databasename> > databse.sql

Then copy this to your PC and run the mysql client

mysql> create <databasename>;
mysql use <databasename>;
mysql> source > database.sql;

You now have a local copy and can use one of the abovementioned tools or ODBC to import into access

Moving from SQL to Access because SQL is “inefficient” is like buying a new car because the old one is too cold, when what you should do is think about closing the windows.

More like buying a donkey because someone forgot to put petrol in your new car.

Access is junk – stay clear.

If you MUST use Access then find an ODBC SQL Query tool. This will allow you to make an ODBC connection to Access and perform SQL queries on it. You may have to slightly modify the syntax of the SQL query from the version you are sent, but it will work.

Hi

If the sql file isn’t too big and the data isn’t sensitive then if you’re stuck you can email it to me and I’ll email it back to you in access format. My email is in my profile. It shouldn’t take long (unless you’re dealing with millions of records).

Better still: just post the database schema here along with the slow queries and we’ll tell you exactly what is wrong with it.

alphabet – it’s not a massive database, that would be a great help if you could do that. your email is not in profile though.

but DavidB your option sounds like it would work too – thanks, i’ll look into that if i alphabet cant help.

I’m currently doing the opposite of what you’re after – moving loads of access code to sql. Access SQL is different to proper SQL but similar enough to work without too much bother unless the code you’ve been sent is more complex than a simple query, eg. a series of stored procedures, functions etc – in this case it will require loads of work (as I’m finding) and it won’t be easy.

Good luck!

If it’s just a one off thing then I’d import it into access via csv as mentioned above.

You can do loads with access without getting programmers involved – I’ve used absolutely massive databases with fat tables of several million records and still got results out of access.

Sorry I thought it was. My email is alphabetical at btinternet dot com

is there a simple way to host the SQL database locally so that I can connect a utility to convert?

Got to agree with most of the above that you’d be better off getting the whole thing working in SQL if poss, but to answer your question more directly, try googling for WAMP (or MAMP if you want to host locally on a Mac).

Sorry I thought it was. My email is alphabetical at btinternet dot com

thanks – emailed it over now. no rush 🙂

you could use sql express linky

this is a free + cut down version of microsoft sql, using the SQL query analyzer you should be able to run a *.sql file

Really, just post the table structure and the query that is running slow. One of us geeks will point out exactly what is wrong without all this faffing about with Access (which is ultimately not going to tell you anything).

What makes you think they are charging you over the odds anyway? Maybe they are, maybe not, how sure are you? Are you sure you are aware of all the issues? I think that on the evidence that you cannot figure out how to do this yourself, then maybe not. I think you would be better off (a) talking with them to understand why it is more work than you expect and (b) discussing if there are any other possiblities that are easier.

Anyway, if you really don’t trust them then I’m sure there are tools to do GUI query building for MySql in an ‘access like’ way if you look around. This is better than using Access because, apart from Access not really being a ‘proper’ RDB, they are likely to say that your optimisations do not translate to MySql.

On the other hand, if the database does contain personal information, send it to me and I’ll forward it to the daily mail 🙂

Webyog for mySql is a decent wysiwyg free front end if you want to go down that route. Still a reasonable jump from Access though IME

btw: MS SQL Server doesn’t use exactly the same sql as mySql – it won’t be cross compatible without fiddling

Just to let everyone know that I’m getting errors when importing the /sql file into MySQL. I’ve also tried to import it into MS SQL Express but am getting errors there too (as acjim has pointed out).

I’ve done alot of what you’re trying to do and no matter how compatible it’s meant to be it is always a PITA.

So it doesnt translate in MS SQL Express either – thanks for trying Alphabet. I had this on the list of possibilites. Any ideas where the problem lies then??

the problem doesnt lie with the database itself, it performs as it was designed to do so. as i said earlier the initial draft wasnt thorough enough so we need to develop the database further to improve functionality. i dont think the developers are ripping us of – i just want to figure out exactly what it is i require hence why i want the data in Access to do some testing/play around with the dataset because that’s the software my team & i know best/already have installed on our server.

On the other hand, if the database does contain personal information, send it to me and I’ll forward it to the daily mail

😆

If you are looking at further development then go from your process end not the data end – what the business requires / how the business works should inform the data not the other way around.

If you still want some data to play with ask your suppliers for some test extracts of the tables you want as csv or even xl files – a months worth for example – then play with these to develop your spec.

Otherwise can you not get remote ODBC access? I assume you have the mySQL database available via a web front end or client? If so then ODBC access should be just a question of getting the correct server details and the login authorisation to go with it.