Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2002
    Posts
    154

    Question Unanswered: Using Access as front end, mySQL as back end

    Does anyone out there know if you use Access as a front end, and mySQL as a back end, can you keep the Access front end replicated, or do you have to do the copy and replace thing to update design changes in Access in this instance? Does Access care if it is being used as the data store in this case, or can I run Synchronize replicas as normal if the tables are in mySQL? Thank you for your time and your responses.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    101% you can use A as frontend to mySQL - works great!
    just keep practising those pass-thru query skills.

    sorry i can't comment on replication: i don't do it.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Using a back end means by definition there is a plit between the data and the presentation (forms reports etc). Access works well as a either an all in solution (data + presentation for a small ish number of users say 5..50) AND a front end to an SQL engine.

    But if you want to use replication and other such technologies I'm pretty sure you need to be using SQLServer from Microsoft. You cna do the same basic effet but you'll have to work at it. Why would you want to use replciation in an MySQL / Access environment. Do you really need 2 way deployment of data copies or can you handle it with a bit better design?

    If only I could persuade my employers to switch to MySQL (or even SQL Server for that matter [ah dream on]).

    Incidently are you aware that most copies of Office XP / Access 2002 come with a limited version of SQL Server.

  4. #4
    Join Date
    Nov 2002
    Posts
    154

    Cool Why I need to replicate

    Right now, I do it as an easy way to keep users from making design changes, though I know Access security can handle that too. Also, more importantly, it is an easy way to implement design changes, as I work the same hours as the users. I am not handling the MySQL backend, a guy in another state is--in the home office in Kansas (I am in Los Angeles). I think that he is just planning on linking tables from the Access front end to the MySQL backend, if that is possible--I can't imagine making pass through queries for each of the dozens of reports and forms in my database application. Yet another possible reason for replication is that right now, our two offices are connected via a VPN, and Access sending the whole 150 Mb file everytime someone makes a change in Kansas necessitated us kit-bashing a terminal services solution that I don't particularly care for (everyone is running the application on the server--it looks bad because it is using the server's video card and then there are the security concerns). I would like to try dropping an Access front end on a server in their office and seeing if it would work as well, the MySQL backend being here.

  5. #5
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Just buy a web server slap a ASP.NET front-end on it and utilize the Microsoft SQL Server Desktop Engine to convert your Access DB's to SQL Servers and you're good to go... the transition will be tough but the benefits will be great.
    -Warren
    Hack the Planet.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the transition will be tough but the benefits will be great.
    and wqould do no harm to your career prospects aswell

  7. #7
    Join Date
    Nov 2002
    Posts
    154

    Cool Access and asp.net

    Unfortunately, I do not know anything about asp.net, though I suppose that I could do some reading to teach myself. I am not sure that I can get the bosses to shell out another 2k or so for a web box but I also have a few issues concerning MySQL for me. I use a lot of transaction processing and I insist on referential integrity as a rule, so I am not sure how this would affect things. I could ask the new guy in Kansas but, quite frankly, I just don't like him. What can I say, I'm only human. But, back to asp.net--what you are talking about is converting the whole thing to some sort of web-based application, right? Wouldn't that mean ditching access' quick deployment capabilities for having to spend lots of time creating web pages that approximate the way that things look now? I have a little experience in making web pages, programming in java, c++, vb, perl, javascript, but I think that it would take a hideous amount of time to program web pages for all the reports and forms that I have(literally dozens) and make them act and look just like the current Access application. Thank you for your responses and time in advance, and especially to those who have already responded and helped me.

  8. #8
    Join Date
    Jan 2005
    Posts
    1

    Acces Front End And Mysql Backend

    HI

    acces is wonderful front end no problem with mysql .i use it in a multi user enviroment and use myisam table as default . it is easier to backup myisam then innodb . special locking issues are address by code .

    you dont need pass throug queries . just use function as a parmeter and pass
    the parameter through the function to the query (in the criterion part of the query use function name eg / from_date() and pass the parameter through the function)
    also remember to add timestamp field to each table in mysql table and primary key to allow multiuser enviroment

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Access as a front end

    Another option might be:

    1. You can use Citrix. We use citrix and it works great but there is a cost to it.
    2. "Spawn" the database on your working drive. You can see my posts on "Spawning a database" which also works nicely. Basically, it allows you to make changes to the *.mdb on your source drive, create a new *.mde, and copy it to the working drive without having users get out of the application.

    I found Replication to not be so fun to work with in Access. Although it has it's benefits, you end up with users who don't synchranize with the main database for several days/weeks (unless you setup an automated synchranization plan.) I also found that users like to make changes in their synchranized version. Not sure how replication works with MySQL as backend tables. I know that Access likes to create an awful primary key of a large negative number (i.e. -2343434333) to a large positive number (i.e. 23434343322) to account for changes in the synchranization process on the records.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •