Connecting to SQL Server table

  • Rather than using the upsize wizard to connect tables to a backend SQL server, how would i go about linking an Access Database to an existing table on an SQL server?

    Thanks, Phil

  • Within Access, using the menu, File/Get External Data/Link Tables... then select "Files of type" (at the bottom of the "Link" window) and click on "ODBC Databases."

     

    [font="Comic Sans MS"]Vic[/font]
    www.vicrauch.com

  • Will this method still work if the Access file is stored on a file server and accessed via a network drive?

    Thanks for your reply.

  • Yes

    [font="Comic Sans MS"]Vic[/font]
    www.vicrauch.com

  • What about if I need to do this in VB6?

  • Having linked the Tables as above they are prefixed with dbo. unlike your other linked access tables.

    I use the following to remove the dbo. and also the other function to refresh the link after having made changes to the SQL database.

    Sub RemoveDBO()

        Dim tbl As TableDef

        For Each tbl In CurrentDb.TableDefs

            If Len(tbl.Connect) > 0 Then

                tbl.Name = Replace(tbl.Name, "dbo_", "")

            End If

        Next

        Set tbl = Nothing

        MsgBox "Remove Complete"

    End Sub

    Function RefreshODBC() As Collection

        Dim tdf As TableDef, db As Database

        Set db = CurrentDb

        db.TableDefs.Refresh

        For Each tdf In db.TableDefs

            With tdf

                If Len(.Connect) > 0 Then

                    If Left$(.Connect, 4) = "ODBC" Then

                      tdf.RefreshLink

                    End If

                End If

            End With

        Next

        Set tdf = Nothing

        Set db = Nothing

        MsgBox "Refresh Complete"

    End Function

    HTH

    Richie

     

  • Is there a way to utilize SQL Server indexes from MS Access?

    Thanks

  • Why not create a view in SQL and link that as a table - it has the speed and power of SQL. Treat the View/Query as any other table object.

  • Thanks - useful info.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply