You are here: > ESRI Forums > arcgis desktop discussion forums > Thread Replies

ArcGIS Desktop Discussion Forums

ArcGIS Desktop - ArcObjects Visual Basic for Application (VBA) forum

Three ways to connect ArcMap to SQL Server   K G Oct 11, 2002
Re: Three ways to connect ArcMap to SQL Ser...   Bridget Beesley Jan 06, 2003
Re: Three ways to connect ArcMap to SQL Ser...   K G Jan 07, 2003
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Three ways to connect ArcMap to SQL Server 
Author K G 
Date Oct 11, 2002 
Message Alright, this one isn't really a question but an answer. The question was how do you get ArcMap and SQL Server 2000 to play nice with each other - or more specifically - how can I test whether a value entered into an ArcMap user form is already present in an SQL Server table? Much thanks to Olivier Damanet for his patience and help in getting me through this issue.
I have come up with 3 ways to go about this: through ArcCatalog, through an Access .mdb, and finally through a direct connection to SQL Server.
If anyone knows a fourth way (an Access .adp perhaps) please respond and post. 
 
Private Sub txtCollectID_AfterUpdate_thru_Access()
'This method uses an Access .mdb that has a link to the SQL Server Collection table through ODBC
'It checks to see whether a record exists in the Collections db already based on the Collection field
On Error GoTo MyError
    dblCollID = Val(txtCollectID.Value)
Dim m_adoCon As ADODB.Connection
    Set m_adoCon = New ADODB.Connection
Dim strSQL As String
Dim m_accWS As IWorkspace
Dim sPath As String
    'Modify the path to the .mdb file accordingly
sPath = "C:\Kevin\Arc_VBA_testing\VAFWIS.mdb"
    'Verify that file exists
  If Dir(sPath) = "" Then
        Debug.Print "file not found : " & sPath
        Exit Sub
    End If
m_adoCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\Kevin\Arc_VBA_testing\VAFWIS.mdb"
m_adoCon.Open

Dim ADOrst As ADODB.Recordset
    Set ADOrst = New ADODB.Recordset
    Set ADOrst.ActiveConnection = m_adoCon
strSQL = "Select * from dbo_Collections where Collection = " & dblCollID
ADOrst.Open strSQL, m_adoCon, adOpenForwardOnly, adLockOptimistic
If (Not ADOrst.EOF) Then
    frmVAFWIS.lblNotifyYes.Visible = True
Else
    frmVAFWIS.lblNotifyNo.Visible = True
End If
ADOrst.Close
    Set ADOrst = Nothing
m_adoCon.Close
    Set m_adoCon = Nothing
MyError:
If Err.Number <> 0 Then
    MsgBox "There was an error " & Err.Description
End If
End Sub


Private Sub txtCollectID_AfterUpdate_thru_SQL_Server()
'This method uses a more direct connection to the SQL Server Collection table through OLE DB
'It checks to see whether a record exists in the Collections db already based on the Collection field
On Error GoTo MyError
dblCollID = Val(txtCollectID.Value)
'++ Create and populate a new property set
  Dim pPropset As IPropertySet
    Set pPropset = New PropertySet
  pPropset.SetProperty "CONNECTSTRING", "Provider=SQLOLEDB;Data source=CARL;Initial Catalog=VAFWIS;User ID=sa;Password=gis"
'++ Create a new workspacefactory/workspace
  Dim pFeatureWorkspace As IFeatureWorkspace
  Dim pWorkspaceFact As IWorkspaceFactory
      Set pWorkspaceFact = New OLEDBWorkspaceFactory
      Set pFeatureWorkspace = pWorkspaceFact.Open(pPropset, 0)
'Open the table
    Dim pTable As ITable
        Set pTable = pFeatureWorkspace.OpenTable("Collections")
'Set up the query
    Dim pQueryFilter As IQueryFilter
        Set pQueryFilter = New QueryFilter
    pQueryFilter.WhereClause = "Collection = " & dblCollID
If pTable.RowCount(pQueryFilter) > 0 Then
    frmVAFWIS.lblNotifyYes.Visible = True
Else
    frmVAFWIS.lblNotifyNo.Visible = True
End If
MyError:
If Err.Number <> 0 Then
    MsgBox "There was an error " & Err.Number
End If
End Sub


Private Sub txtCollectID_AfterUpdate_thru_ArcCatalog()
'This method use a link to the SQL Server Collection table created through ArcCatalog through OLE DB
'It checks to see whether a record exists in the Collections db already based on the Collection field
dblCollID = Val(txtCollectID.Value)
On Error GoTo MyError
 Dim pFeatureWorkspace As IFeatureWorkspace
 Dim pWorkspaceFact As IWorkspaceFactory
          Set pWorkspaceFact = New OLEDBWorkspaceFactory
      Dim pWorkspace As IWorkspace
      '-- connection file
      Dim sFile As String
      sFile = "C:\Documents and Settings\gis\Application Data\ESRI\ArcCatalog\ConnectionToCarl.odc"
        Set pFeatureWorkspace = pWorkspaceFact.OpenFromFile(sFile, 0)
 
Dim pTable As ITable
    Set pTable = pFeatureWorkspace.OpenTable("tblSQL")
'Set up the query
Dim pQueryFilter As IQueryFilter
    Set pQueryFilter = New QueryFilter
pQueryFilter.WhereClause = "Collection = " & dblCollID
If pTable.RowCount(pQueryFilter) > 0 Then
    frmVAFWIS.lblNotifyYes.Visible = True
Else
    frmVAFWIS.lblNotifyNo.Visible = True
End If
 Exit Sub
MyError:
If Err.Number <> 0 Then
    MsgBox "There was an error " & Err.Number
End If
End Sub
 
  K G
Wannabe GIS Superstar 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Three ways to connect ArcMap to SQL Server 
Author Bridget Beesley 
Date Jan 06, 2003 
Message So Kevin, is it possible then to have a geodatabase in ArcMap (having only 1 user edit data in the ArcMap environment), and also have a linked (connected) table to the attributes (I don't care about the spatial data) in a SQL server table? 
  Bridget Beesley
GIS Analyst/Applications Developer
SC Department of Commerce

 
   
Report Inappropriate Content • Top • Print • This Forum is closed for replies.    
Subject Re: Three ways to connect ArcMap to SQL Server 
Author K G 
Date Jan 07, 2003 
Message Bridget,

I don't really do much work with geodatabases (mostly coverages and shapefiles) but I would say, yes, that this is possible. You may need to create a DSN and/or ODBC connection to the external table. This can be done through windoze or through ArcCatalog. If it isn't possible to actually link the table and have it visible in ArcMap you can certainly use SQL to join the external table as part of a query to gather information from the outside table at runtime. 
  K G
Wannabe GIS Superstar