Is it possible to attach a SQL Server table to Access without using a
DSN? Thanks.
DSN? Thanks.
-
-
Re: Attaching SQL Table w/o DSN
Yes. You would need to supply all of the connection string info in the
connection properties, rather than using a DSN name.
I thought I had an example handy, but I don't. Try searching google / forums
with dsn-less...
- found one example:
DRIVER={MS SQL-Server};UID=sa; PWD=;DATABASE=p ubs;SERVER=myMa chine
HTH,
Corey
Marty wrote:[color=blue]
>Is it possible to attach a SQL Server table to Access without using a
>DSN? Thanks.[/color]
--
Message posted via AccessMonster.c om
-
Re: Attaching SQL Table w/o DSN
Access does not let me change the connection string. When I change it,
it changes back when I exit the property value.
Comment
-
Re: Attaching SQL Table w/o DSN
"Marty" <mbaril68@hotma il.com> wrote in message
news:1141338346 .573785.173750@ e56g2000cwe.goo glegroups.com.. .[color=blue]
> Access does not let me change the connection string. When I change it,
> it changes back when I exit the property value.[/color]
Hi Marty
This is a pretty standard question and there must be many examples posted.
You do realize that you cannot change this connection string by going in to
the design view of the linked table, don't you? (Access warns you of this)
Most of the posts to this group assume that you understand vba coding and
your question doesn't make it clear whether you are OK with this or not.
Here is an example where code is used to set up a couple of linke tables by
mapping the SQL table 'dbo.Product' to the linked Access table 'tblProducts'
and 'dbo.Order' to 'tblOrders'.
If this all seems incomprehensibl e, let us know.
Code follows:
Public Function ReLinkTables() As Boolean
On Error GoTo Err_Handler
Dim strConnect As String
Dim strTableList As String
strConnect = "ODBC;Driver={S QL Server};" & _
"Server=MyServe r;" & _
"Database=MyDat abase;" & _
"Uid=MyLogin;Pw d=MyPassword;"
' Table list is in pairs like "SQL_Server|Lin k_Table;"
strTableList = "dbo.Customer|t blCustomers;" & _
"dbo.Product|tb lProducts;" & _
"dbo.Order|tblO rders"
If DeleteLinks(str TableList) Then
If LinkTables(strT ableList, strConnect) Then
ReLinkTables = True
End If
End If
Exit_Handler:
Exit Function
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Private Function LinkTables(strT ableList As String, strConnect As String) As
Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim astrTables() As String
Dim strTwoTables As String
Dim strSqlServerTab le As String
Dim strLinkTable As String
Dim lngPosition As Long
Dim lngCount As Long
Set dbs = CurrentDb
astrTables = Split(strTableL ist, ";")
For lngCount = 0 To UBound(astrTabl es())
strTwoTables = astrTables(lngC ount)
lngPosition = InStr(strTwoTab les, "|")
If (lngPosition > 0) And (Len(strTwoTabl es) > lngPosition) Then
strSqlServerTab le = Mid$(strTwoTabl es, 1, lngPosition - 1)
strLinkTable = Mid$(strTwoTabl es, lngPosition + 1)
Set tdf = dbs.CreateTable Def(strLinkTabl e, dbAttachSavePWD )
tdf.Connect = strConnect
tdf.SourceTable Name = strSqlServerTab le
dbs.TableDefs.A ppend tdf
Set tdf = Nothing
End If
Next lngCount
LinkTables = True
Exit_Handler:
On Error Resume Next
If Not tdf Is Nothing Then
Set tdf = Nothing
End If
If Not tdf Is Nothing Then
Set dbs = Nothing
End If
Application.Ref reshDatabaseWin dow
Exit Function
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Private Function DeleteLinks(str TableList As String) As Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim astrTables() As String
Dim strTwoTables As String
Dim strLinkTable As String
Dim lngPosition As Long
Dim lngCount As Long
Dim blnError As Boolean
Set dbs = CurrentDb
astrTables = Split(strTableL ist, ";")
For lngCount = 0 To UBound(astrTabl es())
strTwoTables = astrTables(lngC ount)
lngPosition = InStr(strTwoTab les, "|")
If (lngPosition > 0) And (Len(strTwoTabl es) > lngPosition) Then
strLinkTable = Mid$(strTwoTabl es, lngPosition + 1)
If TableExists(str LinkTable) Then
Set tdf = dbs.TableDefs(s trLinkTable)
If Len(tdf.Connect ) > 0 Then
dbs.TableDefs.D elete tdf.Name
Else
' This is not a linked table
MsgBox "Cannot delete table '" & strLinkTable & "'",
vbExclamation
blnError = True
End If
Set tdf = Nothing
End If
End If
Next lngCount
If Not blnError Then
DeleteLinks = True
End If
Exit_Handler:
On Error Resume Next
dbs.TableDefs.R efresh
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Application.Ref reshDatabaseWin dow
Exit Function
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Private Function TableExists(str TableName As String) As Boolean
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If tdf.Name = strTableName Then
TableExists = True
Exit For
End If
Next tdf
Exit_Handler:
On Error Resume Next
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Comment
-
Re: Attaching SQL Table w/o DSN
Thanks. That worked perfectly. Is there a property to identify system
tables other then the prefix "MSys"?
Comment
-
Re: Attaching SQL Table w/o DSN
"Marty" <mbaril68@hotma il.com> wrote in message
news:1141343359 .764166.122250@ z34g2000cwc.goo glegroups.com.. .[color=blue]
> Thanks. That worked perfectly. Is there a property to identify system
> tables other then the prefix "MSys"?[/color]
Not sure how it is connected to the original question, but there is a
constant in the DAO object library:
DAO.TableDefAtt ributeEnum.dbSy stemObject
so you could write
?IIF((((Current db.TableDefs(st rMyTable).Attri butes) AND
dbSystemObject) =0),"No","Yes")
to check this, but the only reason I could think for not trusting the "MSys"
prefix is that you suspect your users might be naming tables in a weird way
to spite you. And if this is the case, they may be able to set the
attributes to fake a system table.
But why do you ask?
Comment
Comment