• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1497
  • Last Modified:

Establish ODBC connection using access VB

I am using the attached code to establish an ODBC connection in the ODBC Data source administrator.  This enables me to then establish a link to the tables within the SQL server using VBA.  The issue I have is I must change the:
change the default database to:
to being unchecked or make the value in the combo box to be the value for this given database.
I do not have access to the registry to look and see how the atribute must be established to turn this value off.

So the question is what do I have to add to the strAttributes code to remove the
Sub RegisterDatabaseX()
 
   Dim dbsRegister As Database
   Dim strAttributes As String
   Dim errLoop As Error
 
   ' Build keywords string.
   strAttributes = "Database=Temp" & _
      vbCr & "Description=Temp Database" & _
      vbCr & "OemToAnsi=No" & _
      vbCr & "Server=ngsdb.spr.navistar.com" & _
      vbCr & "Trusted_Connection=Yes" & _
      vbCr & "QuotedId=No" & _
      vbCr & "QuotedId=No" & _
      vbCr & "AnsiNPW=No"
      
   ' Update Windows Registry.
   On Error GoTo Err_Register
   DBEngine.RegisterDatabase "Temp", "SQL Server", True, strAttributes
   On Error GoTo 0
   Exit Sub
 
Err_Register:
 
   ' Notify user of any errors that result from
   ' the invalid data.
   If DBEngine.Errors.Count > 0 Then
      For Each errLoop In DBEngine.Errors
         MsgBox "Error number: " & errLoop.Number & _
            vbCr & errLoop.Description
      Next errLoop
   End If
   Resume Next
End Sub

Open in new window

DSN-Config-Screen-Shot.JPG
0
Joe Overman
Asked:
Joe Overman
  • 3
1 Solution
 
Jim P.Commented:
Add in a database line:
  strAttributes = "Database=Temp" & _
      vbCr & "Description=Temp Database" & _
      vbCr & "OemToAnsi=No" & _
      vbCr & "Server=ngsdb.spr.navistar.com" & _
      vbCr & "Database=Temp" & _
      vbCr & "Trusted_Connection=Yes" & _
      vbCr & "QuotedId=No" & _
      vbCr & "QuotedId=No" & _
      vbCr & "AnsiNPW=No"

Open in new window

0
 
Jim P.Commented:
I read it backwards:
 strAttributes =  "Description=Temp Database" & _
      vbCr & "OemToAnsi=No" & _
      vbCr & "Server=ngsdb.spr.navistar.com" & _
      vbCr & "Trusted_Connection=Yes" & _
      vbCr & "QuotedId=No" & _
      vbCr & "QuotedId=No" & _
      vbCr & "AnsiNPW=No"

Open in new window

0
 
Joe OvermanEngineerAuthor Commented:
That is just what I needed.  Thanks.  I am not sure why I could not figure that our myself.  Again thanks.
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now