April 3, 2006 at 8:46 am
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
April 3, 2006 at 9:15 am
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
April 3, 2006 at 9:31 am
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.
April 3, 2006 at 9:33 am
Yes
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
April 4, 2006 at 1:50 am
What about if I need to do this in VB6?
April 4, 2006 at 5:03 am
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
April 4, 2006 at 2:07 pm
Is there a way to utilize SQL Server indexes from MS Access?
Thanks
April 4, 2006 at 8:22 pm
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.
April 5, 2006 at 3:44 am
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