Results 1 to 7 of 7
  1. #1
    Randy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    46

    Access to SQL or MySql

    Can anyone point me to a site that will explain in depth how to convert access 2010 (mdb backend) to SQL or MySql. I have read everything I can find on the subject but can't seem to impliment it on my network. I don't have a server, the backend just resides on one of the 3 computers in the network.

    I have used both migration tools for SQL and MySql and pretty much tried everything I can think of. I'm missing something simple and can't seem to figure it out.

    Thanks for your help in advance.



    Randy

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    if your tables are normalized you should just be able to import the data you have into your SQL or MySQL table. If you are using an autonumber fields as your primary key it may be a slightly trickier you would just have to append records in the same order they appear in your database, I honestly don't know what would happen if you had gaps in your primary key (autonumber) field though. The worst case scenario is that you import your data, giving it a new ID field then update all your data to use the new primary key instead of the old.

    I have cut and pasted records from an access table to a SQL table before so you shouldn't really have any trouble other than perhaps with the primary key.

  3. #3
    Randy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    46
    Thank you for your reply. I do have some of the tables in the backend mdb that have autonumbers for the primary keys. I don't know what to do next now.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    try setting up your tables in your new back end SQL or MySQL I don't know which from your post enter some test data to see if your autonumber feature is working, then delete that information and import your access information into those tables and see if your primary keys are preserved, if they are, you're home free, if they're not you will likely have to delete the data you just imported, add another field like 'newpk' make this your autonumbering field in SQL, import your data then for all the child tables for each table you'll have to go through and modify the parent ID field from the old to the new numbering system.

    For instance if you originall had this:

    Code:
    People
    PeopleID PeopleName
    1        PersonA
    3        PersonB
    4        PersonC
    
    PeoplePhones
    PhoneID PeopleID PhoneNo
    1       1       111-222-3333
    2       1       111-222-4444
    3       3       333-444-5555
    4       4       444-555-6666
    And after your import of your people table you had this:

    Code:
    People
    PeopleID PeopleName
    1        PersonA
    2        PersonB
    3        PersonC
    NOTE the autonumber removed a place

    Then you'd have to reimport and allow for a changing people ID with something like this

    Code:
    People
    OldPeopleID PeopleName PeopleID
    1           PersonA    1
    3           PersonB    2
    4           PersonC    3
    Then import your phone number table and anyplace where the personID appears you would have to update with the NEW peopleID.

  5. #5
    Randy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    46
    Sorry, this is really confusing to me. Let me give you a little more information that might help. I first tried SQL Server 2008 RC and was able to export/import the backend to SQL. I could see the tables in the folder tree and run queries against it although I didn't try to run any queries. The problem is, I can't connect my access frontend (mdb) to the SQL data.

    I have followed all sorts of directions on setting up the connection. I can get the connection to work on my desktop, the one with the backend, but when I take the front end to another computer on the network it won't connect.

    I've setup the ODBC connections when appropriate and the System DNS's but the front end will only work on the computer where I initiated the SQL export. I have tried this with both SQL Server 2008 and MySQL.

    Any ideas... and thanks for your reply.

    Randy

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    Ok, there are a few things you can check

    1. Is your ODBC connection set up exactly the same on both computers. If you have given the connection a different name (even if it's a mistyped character) it will not work. The connection has to have the exact same name on both computers. for instance let's say you set up a DSN on your first computer of
    Linking_Criteria
    and on the secondary computer it's in as
    Llnking_criteria (l instead of capital I)
    your odbc connection will not work on both machines.
    2. Does the secondary computer have permission to the folder where the access database is housed? and does it have permission to see the SQL tables?
    3. What type of linking security are you using? Windows Authenticaion? SQL authentication?

    What exactly is your error message when you try to open a table in the secondary workstation?

  7. #7
    Randy is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    46
    rpeare: Thank you for your reply. I am going to start over using SQL Server 2008 RC and try to make the connection again fresh. This may take a little time so I will get back to you with the results as soon as I can.

    Thanks for hanging in there with me.

    Randy

Please reply to this thread with any new information or opinions.

Similar Threads

  1. MySQL & Access
    By Scorpio11 in forum Database Design
    Replies: 3
    Last Post: 05-21-2011, 02:32 PM
  2. Replies: 0
    Last Post: 05-14-2010, 08:43 AM
  3. Using Access and going to Mysql
    By fsmikwen in forum Programming
    Replies: 1
    Last Post: 11-26-2009, 01:15 PM
  4. Access database to Mysql
    By fsmikwen in forum Access
    Replies: 4
    Last Post: 11-16-2009, 05:40 AM
  5. How to Mirror Access in MySQL???
    By s3rvant in forum Database Design
    Replies: 0
    Last Post: 07-28-2009, 08:25 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums