ArcMap连接微软SQL Server数据库三种方法代码

我们的ARC数据库功能强大，可以方便存储空间数据，但是象地理属性数据，社会经济属性等二维数据还是存储在关系型数据库较方便，比如Oracle, SQL Server等。既然数据存储在不同的数据库系统下，那么如何通信呢？本文以实际代码讲解ArcMAP如何连接微软SQL Server的三种方法。

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

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_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