直接用Access語法Select,Insert,Update,Delete不同資料庫

'因是利用Access 連線,所以所有語法都需以Access可接受的語法來做
'範例為單純的Select 語法,可自行將其改成Insert,Update,Delete
'Oracle
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strOraODBCTable As String
Dim ConnStr As String
Dim TableName As String
Dim strSQL As String
'建立連線
ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\db1.mdb;Persist Security Info=False;"
cn.Open ConnStr

TableName = "xx"
strOraODBCTable = "[ODBC;DRIVER={Microsoft ODBC for Oracle};UID=...;PWD=...;SERVER=...;]" & _
".[" & TableName & "]"

strSQL = "Select * From " & strOraODBCTable
'可自行改成Insert ,Update ,Delete語法
rs.CursorLocation = adUseClient
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly

Set Me.DataGrid1.DataSource = rs
DataGrid1.Refresh
End Sub

'SQL Server
Private Sub Command2_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQLODBCTable As String
Dim ConnStr As String
Dim TableName As String
Dim strSQL As String
'建立連線
ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp\db1.mdb;Persist Security Info=False;"
cn.Open ConnStr

TableName = "TEST1"
strSQLODBCTable = "[ODBC;DRIVER=SQL Server;SERVER=...;APP=Visual Basic;WSID=...;DATABASE=...;Trusted_Connection=Yes]" & _
".[" & TableName & "]"
strSQL = "Select * From " & strSQLODBCTable
rs.CursorLocation = adUseClient
rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly
'可自行改成Insert ,Update ,Delete語法
'再利用cn.Execute strSQL 異動資料庫

Set Me.DataGrid1.DataSource = rs
DataGrid1.Refresh

End Sub