should i write an access front end or c# front end?

I think you all are either completely crazy or don't know a damned thing about Access.

It is certainly true that the upsizing tools for SQL Server are easier to use than anything available to you for upsizing to MySQL. But the difficulty of upsizing should not drive your choice of back end, because it's a one-time choice. Choose your back end database based on what works best for you. While SQL Server Express is free, it has a number of limitations that MySQL itself does not have.

As I said in comments, you can export an Access table to MySQL by defining a DSN for your MySQL database and simply using the EXPORT command on the Access file menu. The results will not necessarily be perfect, so you might have to tweak the results, truncate the MySQL table and then insert the real data once the MySQL tables are all in place. Yes, this is more work than the SQL Server upsizing tools, but it's still pretty easy to do.

And it's not something you have to get right on the first go, nor do you have to do it multiple times.

If MySQL is your preferred database engine for your app, then go with it. Using SQL Server entirely because of the more elaborate upsizing tools is really a case of the tail wagging the dog!

+1: SQL Server Express is free, and [2008 R2 supports databases upwards of 10GB](http://www.microsoft.com/express/database/)

OMG Ponies 2010-08-02 01:59:42

why do you say sql server instead of mysql

I__ 2010-08-02 02:01:01

Because you can upsize Access to SQL Server easily. That way SQL Server handles all the data tasks; Access becomes a mere user interface.

duffymo 2010-08-02 02:12:00

well cant you do the same thing with mysql and have access be the front end

I__ 2010-08-02 02:14:26

@I__ yes you can. In one of my projects, we did exactly that. All data and procedures are in MySQL, reports, forms and such are in Access. Not that pretty, but it fit the requirements.

siride 2010-08-02 04:43:57

Of course it can be done with MySQL, but you'll have to work a lot harder. SQL Server has tools to do the upsizing in a single step. You'll just have to do the heavy lifting yourself with MySQL.

duffymo 2010-08-02 09:21:19

duffy, can you please elaborate what do you mean by upsizing and heavy lifting

I__ 2010-08-02 15:40:33

I could be ignorant here, but I thought that the Microsoft tools to upsize database only worked with SQL Server and Access. If you wanted to use any other database, like MySQL, you'd have to write the code to create the tables and indexes and map the data from Access to MySQL yourself. That's the "heavy lifting". It can be done, of course, but you won't have Microsoft tools to help you.

duffymo 2010-08-02 15:50:36

You don't have to write code to upsize Jet/ACE tables to MySQL. Just create a DSN for your MySQL database and then from within Access, in your back end, export each table to the DSN. You'll likely need to tweak data types and that sort of thing, and maybe have a couple of gos at it, but you don't have to script it unless it's something you actually need to do more than once.

David-W-Fenton 2010-08-02 19:38:36

Isn't that what "tweak data types" and "that sort of thing" is? I'll admit I haven't done it, so I don't know.

duffymo 2010-08-02 19:48:31

I don't know what you are referring to with "that" so I can't answer your question.

David-W-Fenton 2010-08-03 19:47:31

"I think you all are either completely crazy or don't know a damned thing about Access." - both may be true. I can see where it'd be easy for a handful of tables (< 5), but if it turned into lots more, with complicated relationships between them, I'd reconsider the choice. That might be a "crazy" reliance on a tool, but without any other requirements it seemed to be a valid enough assumption.

duffymo 2010-08-03 20:53:29

I can't see how it's much of an issue even with lots of tables. Upsizing is a one-time operation and ease of doing so shouldn't drive the choice of back end. Obviously if it's *not* one time, it would need to be scripted, and perhaps that's easier with SQL Server, and thus makes it a lot easier. But I don't see that as a part of the original question. The reason I started out with my original question is that a lot of people are simply unaware of how powerful Access/Jet/ACE are. That is, the EXPORT to a DSN is a pretty easy way to do something that many people would think they have to script.

David-W-Fenton 2010-08-05 18:42:56

"Upsizing is a one-time operation and ease of doing so shouldn't drive the choice of back end." - fair enough. I think this is a good argument. Thank you for a good discussion.

duffymo 2010-08-05 19:19:17

C# is smuch easier than Access? Please explain in much more detail.

Tony Toews 2010-08-04 02:41:18

If you don't know any of both languages, I recommend learn C# over VB for Apps. Don't be confused, VB for Apps are (very) different from VB.NET. It is not a matter of "much easier", but C# is more powerful and strong that VB for Apps, and yes, IMO C# is easier to learn and use than VB for Apps.

Lester 2010-08-06 00:01:34

I just tried an ODBC export to PostGreSQL. My autonumber primary key field became a PostGres integer instead of serial, and did not have the primary key constraint. Indexes on other fields were not carried over. Still I like your suggestion because I can retrieve the DDL, modify it as needed, drop the table, and re-create it. That's a big win for me compared to starting off from scratch in PostGres. Thanks. I'm curious, is there less tweaking involved when you export to MySQL?

HansUp 2010-08-02 22:37:57

It depends on the version of MySQL. I haven't done it very often, actually, so don't have any real details. The SQL Server upsizing tools can get the data types wrong, too, so it's really a non-issue, seems to me.

David-W-Fenton 2010-08-03 19:46:47

ansaurus

tags:

views:

answers:

should i write an access front end or c# front end?

related questions