Results 1 to 9 of 9
  1. #1
    jrdnoland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41

    Convert Access 07 ADO code to SQL Server Express 2008

    I have an excel front end to an access 07 database that uses vba and manages the access database with ado code. Does anyone know anything about converting that code to work with sql server express 2008 or mysql databases.



    I've done some searching but am not finding out much info about transferring the actual code.

    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It would help to see what code you're trying to convert, but generally you'd need a different connection string:

    ConnectionStrings.com - Forgot that connection string? You will find it right here!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jrdnoland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41
    Thanks Paul - I thought the connection string would be different, I was wondering if access sql and sql server sql syntax was the same.

    Here's a small example of what I'm doing:



    Code:
    Dim cn As ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim rst2 As ADODB.Recordset
    Dim rst3 As ADODB.Recordset
    Dim rst4 As ADODB.Recordset
    Dim rst5 As ADODB.Recordset
    Dim rst6 As ADODB.Recordset
    Dim rst7 As ADODB.Recordset
    Dim rst8 As ADODB.Recordset
    Dim rst9 As ADODB.Recordset
    Dim rst10 As ADODB.Recordset
    Dim rst11 As ADODB.Recordset
    Dim rst12 As ADODB.Recordset
    
    
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
    "Data Source=" & c_Drive & c_Folder & c_SubFolderS & c_DataBaseSecondary & ";"
    
    '******************************************************************************************************************************
    'Open Recordset 1 *************************************************************************************************************
    Set rst1 = New ADODB.Recordset
        rst1.Open c_Second_DB_TBL_1, cn, adOpenKeyset, adLockOptimistic, adCmdTable
        ' all records in a table
        'Can only add one record (one sample) at a time to the database
        With rst1
            .AddNew ' create a new record
            ' add values to each field in the record this is after entries have been validated.
            .Fields("Gen_Stock_Code") = Worksheets("INPUTS").OLEObjects("cboGenStockCode").Object.Text
            .Fields("Gen_GOJO_Name") = Worksheets("INPUTS").OLEObjects("cboGenGOJOName").Object.Text
            'numeric
            .Fields("Gen_Numb_INCI_Names") = Worksheets("INPUTS").OLEObjects("cboGenINCINameNumberInput").Object.Text
            .Fields("Gen_Function") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_SubFunction") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Preservation") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Source") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Grade_Link") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Irradiation") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Oxidative") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Corrosive") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Numb_Grade") = Worksheets("INPUTS").OLEObjects("    ").Text
            'numeric
            .Fields("Gen_Numb_Type") = Worksheets("INPUTS").OLEObjects("    ").Text
            'numeric
            .Fields("Gen_Numb_Certifications") = Worksheets("INPUTS").OLEObjects("    ").Text
            'numeric
            .Fields("Gen_Numb_Manufacturers") = Worksheets("INPUTS").OLEObjects("    ").Text
            'numeric
            .Fields("Gen_Numb_Incoming_Tests") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Storage_Shipping") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Flash_Point") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Health") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Flammability") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Reactivity") = Worksheets("INPUTS").OLEObjects("    ").Text
            'numeric
            .Fields("Gen_Numb_PPE") = Worksheets("INPUTS").OLEObjects("    ").Text
            'numeric
            .Fields("Gen_Numb_Revisions") = Worksheets("INPUTS").OLEObjects("    ").Text
            .Fields("Gen_Spec_Status") = Worksheets("INPUTS").OLEObjects("    ").Text
              
            .Update ' stores the new record
        End With
        'close the recordset
        rst1.Close
        Set rst1 = Nothing
    ...etc

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, there are some SQL and function differences, but you don't use any there that I can see. Offhand, I think that would work fine for a SQL Server back end (with the connection string changed of course).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jrdnoland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41
    OK, it may be easier than I thought then.

    The reason I'm considering changing is that this will be a mission critical application and have multiple users. My IT department thinks that Access will get corrupted too easily in this environment. I think it may work OK, any thoughts?

    Thanks for your input!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    How many users? I have no experience with Excel as a front end to Access, but Access is pretty stable as long as it's set up right. The most common cause of corruption is having multiple users accessing the same copy of an MDB front end. With your setup, I wouldn't expect corruption (but keep in mind I've never used it that way). Maybe somebody with some experience with that setup will chime in.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    jrdnoland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41
    My system will provide access for about 50 users. I wouldn't expect more than 3 or 4 users at a time doing anything.

    Some users will be limited to only running sql queries to return info from the database. Others will be able to add records and to modify records using excel, ado, and vba as the front end.

    I plan on load testing it when finished, but began thinking about just upgrading the db to sql server or mysql. I really don't want to, but I don't want access to crash and burn either.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    My gut feeling is that Access would handle that type of thing with ease. I guess it would also depend on the size of the db, but you'd have to have a lot of records before I'd say you should switch.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    jrdnoland is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41
    Thanks Paul, that's what I thought also. I'll finish the project in access and load test it and see what happens.

    I would expect the database to hold about a 1000 records to start and then grow over the years at a rate of about 500 records a year.

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

Similar Threads

  1. Access 2003 code vs Access 2007 Code
    By ralphjramirez in forum Access
    Replies: 5
    Last Post: 11-23-2009, 12:33 PM
  2. Replies: 5
    Last Post: 10-24-2009, 01:16 PM
  3. Convert query sql server for Access
    By webtony in forum Queries
    Replies: 0
    Last Post: 06-23-2009, 02:46 AM
  4. Convert Excel Macro to Access Query
    By crownedzero in forum Queries
    Replies: 1
    Last Post: 06-22-2009, 02:13 PM
  5. Replies: 0
    Last Post: 03-09-2009, 12:20 PM

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