OBDC connection code

[rbowes]

I have an app that hooks up to a SQL Server 7 database.&nbsp;&nbsp;We are in the middle of development with the database so versions come out once every 3 weeks or so.&nbsp;&nbsp;My app, which uses Access 2000 to summarized and analyze data, will link tables through ODBC.&nbsp;&nbsp;My problem is that all code that I have seen regarding refreshing links or creating connections require the DSN and the Database Name.<br>The Connection path I use looks like this:<br><br>strPath = &quot;ODBC;DSN=MyDSN;Database=MyDatabase;UID=MyID;PWD=&quot;<br><br>&nbsp;&nbsp;When my app is distributed to 15 sites there is a chance that the Database Name will change - Especially if for some reason we have to do a major revision for some unforseen reason.&nbsp;&nbsp;Therefore, I want to stay away from hardcoding the Database Name.&nbsp;&nbsp;What I need is for a way to determine the default Database Name configured to the DSN (The DSN name will not be changing).&nbsp;&nbsp;I have tried just passing the DSN and nothing seems to happen, no connection is made.&nbsp;&nbsp;Can anyone help with this one?<br><br>Thanks in advance!<br><br>Bob

 

[DougP]

Don't know if this will help<br>I have SQL server 7 and created a 'System' DSN then I used the code below which did not need the Database name but it requires the Server name<br>-------------------------------------<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim wrkJet As Workspace<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim dbsNorthwind As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim wrkODBC As Workspace<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim conLoop As Connection<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim conPubs2 As Connection<br><br>&nbsp;&nbsp;&nbsp;&nbsp;' Open Microsoft Jet Database object.<br>&nbsp;&nbsp;&nbsp;&nbsp;Set wrkJet = CreateWorkspace(&quot;NewJetWorkspace&quot;, &quot;admin&quot;, &quot;&quot;, dbUseJet)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Create ODBCDirect Workspace object and open Connection<br><br>' objects.<br>&nbsp;&nbsp;&nbsp;&nbsp;Set wrkODBC = CreateWorkspace(&quot;NewODBCWorkspace&quot;, &quot;admin&quot;, &quot;&quot;, dbUseODBC)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set conPubs2 = wrkODBC.OpenConnection(&quot;Connection2&quot;, , True, &quot;ODBC;DSN=Copy Parts Master;SERVER=SMALLBSERVER;UID=DougP;QueryLogFile=Yes&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Enumerate the Connections collection.<br>&nbsp;&nbsp;&nbsp;&nbsp;For Each conLoop In wrkODBC.Connections<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;Connection properties for &quot; & conLoop.Name & &quot;:&quot;<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;With conLoop<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Print property values by explicitly calling each<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Property object; the Connection object does not<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' support a Properties collection.<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;Connect = &quot; & .Connect<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Property actually returns a Database object.<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;Database[.Name] = &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.Database.Name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;Name = &quot; & .Name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;QueryTimeout = &quot; & .QueryTimeout<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;RecordsAffected = &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.RecordsAffected<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;StillExecuting = &quot; & _<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.StillExecuting<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;Transactions = &quot; & .Transactions<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print &quot;&nbsp;&nbsp;&nbsp;&nbsp;Updatable = &quot; & .Updatable<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End With<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Next conLoop<br>----------------------------------------<br>I used a little known function in a query to build the DSN for me.<br>Open a new query and close the Tables slection box then<br>Click &quot;Query&quot; menu and &quot;SQL Specific&quot; click &quot;Pass through&quot;<br>Click &quot;View&quot; Menu then &quot;Properties&quot; then in the &quot;ODBCConnectStr&quot; click the 3 dots button<br>which will allow you to build a connect string, then I copied that and pasted it in the above code.<br><br>OK<br> <p>DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.