Rx_
Nothing In Moderation
- Local time
- Today, 16:52
- Joined
- Oct 22, 2009
- Messages
- 2,803
Where is this Protocol name coming from? The script and server database relocation from a 32bit SQL to 64bit SQL all work fine.
My concern is that the alias DBMSSOCN is being maintained somewhere. If it ever changes, it will affect my program's connection.
Network= appears in linked table Connection string - was not there for 32bit SQL - using exact same script
History MS Access 2010 with DSN-Less linked tables to SQL Server 2008 r2:
Set up a 32bit SQLTest server. (1) The script works perfectlly. (2) Using a remote terminal, SQL Enterprise Manager allows a login for Datamig on SQL Server Authication. (3) the ODBC connection works perfectlly (4) the Server name has one name e.g. SQLTest
Moved DB to a 64bit SQLProduction Server: (1) The script works perfectlly. (2) Using a remote terminal, SQL Enterprise Manager allows a login for Datamig on SQL Server Authication. (3) the ODBC connection works perfectlly (4) the Server name shows as two names e.g. Servername\SQLProduction
My ODBC dialogue (selection Native 10) added this message after connecting with Servername\SQLProduction:
INFO: A network Protocol was found for the DSN server. 'Protocol: DBMSSOCN; Address: DENxxxSQL\denxxxulator' was used to establish the connection.
Sure enough, on my MS Access table connection string also displays the Network=DBMSSOCN that is not in my code's connection string
In the previous SQLTest connection string - the MSAccess connection string was just SQLTest
The VBA Code is shown below - it clearly uses the Servername\SQLProduction for the TableDefs.Connect - but once finished, the actual table Connect property is DBMSSOCN -
Where on SQL Enterprise Manager (or other services) do I find the DBMSSOCN Protocol service?
' This code segment reads a table with a list of MSAccess tables to be linked to SQL Server - it loops through each table and adds a DSN-Less connection string using the custom function ModifiedRefreshDNSLess2.
--- the Function call ModifiedRefreshDNSLess2 (The access table field name that matches the SQL table field name)
Note: denregsql\denregulator is passed in as the server name - but DBMSSOCN is injected in the final Access table connection
Where do I find this association?
My concern is that the alias DBMSSOCN is being maintained somewhere. If it ever changes, it will affect my program's connection.
Network= appears in linked table Connection string - was not there for 32bit SQL - using exact same script
History MS Access 2010 with DSN-Less linked tables to SQL Server 2008 r2:
Set up a 32bit SQLTest server. (1) The script works perfectlly. (2) Using a remote terminal, SQL Enterprise Manager allows a login for Datamig on SQL Server Authication. (3) the ODBC connection works perfectlly (4) the Server name has one name e.g. SQLTest
Moved DB to a 64bit SQLProduction Server: (1) The script works perfectlly. (2) Using a remote terminal, SQL Enterprise Manager allows a login for Datamig on SQL Server Authication. (3) the ODBC connection works perfectlly (4) the Server name shows as two names e.g. Servername\SQLProduction
My ODBC dialogue (selection Native 10) added this message after connecting with Servername\SQLProduction:
INFO: A network Protocol was found for the DSN server. 'Protocol: DBMSSOCN; Address: DENxxxSQL\denxxxulator' was used to establish the connection.
Sure enough, on my MS Access table connection string also displays the Network=DBMSSOCN that is not in my code's connection string
In the previous SQLTest connection string - the MSAccess connection string was just SQLTest
The VBA Code is shown below - it clearly uses the Servername\SQLProduction for the TableDefs.Connect - but once finished, the actual table Connect property is DBMSSOCN -
Where on SQL Enterprise Manager (or other services) do I find the DBMSSOCN Protocol service?
' This code segment reads a table with a list of MSAccess tables to be linked to SQL Server - it loops through each table and adds a DSN-Less connection string using the custom function ModifiedRefreshDNSLess2.
Code:
240 If RecordsCount <> 0 Then
250 rsSQLLinked.MoveFirst
260 For Counter = 1 To RecordsCount
'Debug.Print Counter & "/" & RecordsCount & " Field value " & rsSQLLinked.Fields(0).Value & " " & rsSQLLinked.Fields(2).Value
270 If rsSQLLinked.Fields(2).Value Then ' if Relink checkbox is true then
' Add new linked table here
'280 'Set tdLinked = CurrentDb.CreateTableDef(rsSQLLinked.Fields(0).Value)
280 Set tdLinked = CurrentDb.CreateTableDef(rsSQLLinked.Fields(0).Value, dbAttachSavePWD) ' password persist now
290 [B] tdLinked.Connect [/B]= ModifiedRefreshDNSLess2(rsSQLLinked.Fields(0).Value)
300 tdLinked.SourceTableName = "dbo." & rsSQLLinked.Fields(0).Value
310 CurrentDb.TableDefs.Append tdLinked
320 CurrentDb.TableDefs(rsSQLLinked.Fields(0).Value).RefreshLink
330 CurrentDb.Containers("Tables").Documents.Refresh ' doesn't refresh table icon
340 End If
350 rsSQLLinked.MoveNext
Debug.Print "Error " & Err.Description & Err.Number & " " & rsSQLLinked.Fields(0).Value
360 Err.Clear
370 Next Counter
Code:
10 UID = "Dataxxxx"
20 PWD = "dataxxxx"
40 DataBaseName = "xxxxlatoryDB"
50 strConnectionString = "ODBC;DRIVER=SQL Server Native Client 10.0;" & _
"SERVER=denxxxsql\denxxxulator;DATABASE=" & DataBaseName & ";" & _
"UID=" & UID & ";" & _
"PWD=" & PWD & ";" & _
"Table=DBO." & sLocalName & ";Option=3;"
60 ModifiedRefreshDNSLess2 = strConnectionStringWhere do I find this association?
Last edited:
