Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Access Forms to SQL BE (Access 2003 - SQL 2005)

    Hi,

    Unfortunately I know next to nothing about Access and very little about SQL, however, Ive a project of converting an Access database to SQL and retaining the Access frontend.

    Ive successfully upsized the Access backend tables to an Access project (adp) and copied over the various macros and forms. The forms generally do open but often with error messages, most of which Ive attached.

    The upsize all went through without a hitch and I knew it was about to get difficult.

    Could some guide me as to the next few steps to get the forms connected to the SQL backend please, and anything I need to bear in mind when doing so.

    Cheers.
    Lee
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Forms to SQL BE (Access 2003 - SQL 2005)

    I'd use a copy of the original database (.mdb) as frontend. Delete all the tables, then use File | Get External Data | Link Tables to link to the tables you transferred to SQL Server. Most things should work as before.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Access Forms to SQL BE (Access 2003 - SQL 2005)

    Hi Hans,

    Many thanks for the reply, sorry the response is a little late, its been very hectic.

    I'll give it a go and report the outcome.

    Thanks again.

    Regards.
    Lee

  4. #4
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Access Forms to SQL BE (Access 2003 - SQL 2005)

    Edited by HansV to replace Word doc with the picture it contained - saves space and clicks

    Hi Hans,

    That does generally seem to work, however I get the odd message now as attached. Also when opening a login page, I recieve the error message, "Error in loading DLL" and am referred to the following code; In particular the line - CurrentProject.Connection.Execute strSQL, at the end, with the word "Connection" highlighted.


    Public Function LogOpen()
    Dim strSQL As String
    Dim Name As String
    Dim strDate As String
    Forms![frmLogin].[list].SetFocus
    CurrentProject.Connection.Execute strSQL
    End Function

    Is there a way I can get around this maybe?

    Regards,
    Lee
    Attached Images Attached Images
    • File Type: png x.png (4.0 KB, 0 views)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Forms to SQL BE (Access 2003 - SQL 2005)

    Try clicking Debug in the error message shown in your screenshot to see where the problem occurs.

    About the other problem: see "Error in Loading DLL" at currentproject.connection on different machine

  6. #6
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Access Forms to SQL BE (Access 2003 - SQL 2005)

    Great !

    Many thanks for that Hans.

    I'll post how I get on as soon as I can.

    Best Regards.
    Lee

Posting Permissions

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