By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,225 Members | 1,434 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,225 IT Pros & Developers. It's quick & easy.

Attaching SQL Table w/o DSN

P: n/a
Is it possible to attach a SQL Server table to Access without using a
DSN? Thanks.

Mar 2 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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=pubs;SERVER=myMachine

HTH,

Corey

Marty wrote:
Is it possible to attach a SQL Server table to Access without using a
DSN? Thanks.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200603/1
Mar 2 '06 #2

P: n/a
Access does not let me change the connection string. When I change it,
it changes back when I exit the property value.

Mar 2 '06 #3

P: n/a
"Marty" <mb******@hotmail.com> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...
Access does not let me change the connection string. When I change it,
it changes back when I exit the property value.


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 incomprehensible, 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={SQL Server};" & _
"Server=MyServer;" & _
"Database=MyDatabase;" & _
"Uid=MyLogin;Pwd=MyPassword;"
' Table list is in pairs like "SQL_Server|Link_Table;"
strTableList = "dbo.Customer|tblCustomers;" & _
"dbo.Product|tblProducts;" & _
"dbo.Order|tblOrders"
If DeleteLinks(strTableList) Then
If LinkTables(strTableList, 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(strTableList 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 strSqlServerTable As String
Dim strLinkTable As String
Dim lngPosition As Long
Dim lngCount As Long
Set dbs = CurrentDb
astrTables = Split(strTableList, ";")
For lngCount = 0 To UBound(astrTables())
strTwoTables = astrTables(lngCount)
lngPosition = InStr(strTwoTables, "|")
If (lngPosition > 0) And (Len(strTwoTables) > lngPosition) Then
strSqlServerTable = Mid$(strTwoTables, 1, lngPosition - 1)
strLinkTable = Mid$(strTwoTables, lngPosition + 1)
Set tdf = dbs.CreateTableDef(strLinkTable, dbAttachSavePWD)
tdf.Connect = strConnect
tdf.SourceTableName = strSqlServerTable
dbs.TableDefs.Append 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.RefreshDatabaseWindow
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Private Function DeleteLinks(strTableList 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(strTableList, ";")
For lngCount = 0 To UBound(astrTables())
strTwoTables = astrTables(lngCount)
lngPosition = InStr(strTwoTables, "|")
If (lngPosition > 0) And (Len(strTwoTables) > lngPosition) Then
strLinkTable = Mid$(strTwoTables, lngPosition + 1)
If TableExists(strLinkTable) Then
Set tdf = dbs.TableDefs(strLinkTable)
If Len(tdf.Connect) > 0 Then
dbs.TableDefs.Delete 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.Refresh
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Application.RefreshDatabaseWindow
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Private Function TableExists(strTableName 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
Mar 2 '06 #4

P: n/a
Thanks. That worked perfectly. Is there a property to identify system
tables other then the prefix "MSys"?

Mar 3 '06 #5

P: n/a
"Marty" <mb******@hotmail.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...
Thanks. That worked perfectly. Is there a property to identify system
tables other then the prefix "MSys"?

Not sure how it is connected to the original question, but there is a
constant in the DAO object library:

DAO.TableDefAttributeEnum.dbSystemObject

so you could write

?IIF((((Currentdb.TableDefs(strMyTable).Attributes ) 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?
Mar 3 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.