Connecton 1

[EscapeUK]

I have an Access DB where several tables are linked to SQL tables.&nbsp;&nbsp;<br><br>The problem is that when I run the DB,&nbsp;&nbsp;i get the SQL Server login box asking me for a user id and password.&nbsp;&nbsp;All of that is fine,&nbsp;&nbsp;but how do I code a solution where I do not get the box appearing on screen.&nbsp;&nbsp;(note i do not want a trusted connection)<br><br>Thanks

 

[kathryn]

Can you accept having the password and ID hardcoded into the connection string?&nbsp;&nbsp;If so, that is the easiest way to handle it, I believe.<br><br>Kathryn

 

[EscapeUK]

Yes I can how do I do this

 

[kathryn]

Here is some code that I use.&nbsp;&nbsp;Be careful with the line breaks when you copy it.&nbsp;&nbsp;<br><br>This function returns a list of all your tables and their connection strings.&nbsp;&nbsp;To use it, open a module and paste the code in.&nbsp;&nbsp;Then type G while holding down the control key, to bring up&nbsp;&nbsp;the debug window.&nbsp;&nbsp;In the bottom pane of the window that appears type ?GetConnectStringAll<br><br>*****Begin Code*****<br>Function GetConnectStringAll() As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim dbs As Database, tdf As TableDef<br><br>&nbsp;&nbsp;&nbsp;&nbsp;' Return reference to current database.<br>&nbsp;&nbsp;&nbsp;&nbsp;Set dbs = CurrentDb<br>&nbsp;&nbsp;&nbsp;&nbsp;' cycle thru tables<br>&nbsp;&nbsp;&nbsp;&nbsp;For Each tdf In dbs.TableDefs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If tdf.Connect &lt;&gt; &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GetConnectStringAll = GetConnectStringAll & tdf.Name & &quot;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot; & tdf.Connect & vbCrLf<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Next tdf<br><br>End Function<br><br>*****End Code*****<br><br>When you run the code you will get a list of tables and connection strings.&nbsp;&nbsp;You should see that there was no ID or password parameters in the connection string.&nbsp;&nbsp;You will need to reset the connection string to include the password and ID.<br><br>I have code that will do this, but it is really complicated because of some weird quirks in Access.&nbsp;&nbsp;Are you using a DSN for your link?&nbsp;&nbsp;Can you set the ID and password there?&nbsp;&nbsp;What exactly is your connection string?<br><br>If you can't do it through the DSN, I'll post the code that I use.<br><br>Kathryn

 

[EscapeUK]

I am i using DSN ummm dunno,&nbsp;&nbsp;i'm new to this.<br>is'nt there just a couple of lines that will do the link automatically.&nbsp;&nbsp;&nbsp;Loads of people must link up SQL tables in access.<br><br>oh year,&nbsp;&nbsp;the tables link to are on a server,&nbsp;&nbsp;so there is not reall any complicated connections,&nbsp;&nbsp;and i will be using the standard sa user name and blank password

 

[Phooey]

Try this:<br><br><font color=blue>DSNname = YourDSNName</font><br><br><font color=green>'Set ODBC connection and open the connection to the SEP Database</font><br><font color=blue>Set cnnImport = New ADODB.Connection<br>cnnImport.Open &quot;DSN=&quot; & DSNname & &quot;;UID=UserID;PWD=Password;&quot;<br></font><br><br>The UserID (UID) and Password (PWD) are passed as above.<br>You can then use Kathryn's code to link the tables.<br>