Hi, I am trying to do a workaround while I'm trying to resolve an issue
with
the SQL Server implementation that does the same thing, but is currently
broken.
Basically, I have an MDB file with two tables in it, and two tables in SQL
Server that look just like the tables in the access database
Both tables have an auto increment field on the Access side. I want to
select the new records from the Access db and insert them into the SQL DB.
I can get to both DBs from Excel. I can write code that pulls the new
records from Access into Excel, now I want to push those records into SQL
Server.
It seems to me I don't really even need the worksheet. If I got a record
set
from Access, and I know it's the same structure as what's in SQL Server,
is
there a way to insert the record set from Access in SQL Server?
This doesn't give me any errors ....
Private Sub MoveDataFromAccessToSQLServer()
Dim ADODB_ACConnection As New ADODB.Connection
Dim ADODB_SQConnection As New ADODB.Connection
Dim ADODB_ACRecordSet As New ADODB.Recordset
Dim ADODB_SQRecordSet As New ADODB.Recordset
On Error GoTo ExitRoutine
With ADODB_ACConnection
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=<FileName>.mdb;Persist Security Info=False;"
.Open
If .State <> adStateOpen Then
MsgBox "Could not open Access database"
GoTo ExitRoutine
End If
End With
With ADODB_ACRecordSet
.ActiveConnection = ADODB_ACConnection
.Open "SELECT * FROM Experiment WHERE autonumber > 1000"
End With
With ADODB_SQConnection
.ConnectionString = "Provider=SQLNCLI.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=<DBName>;Data
Source=<Server>"
.Open
If .State <> adStateOpen Then
MsgBox "Could not open SQL database"
GoTo ExitRoutine
End If
End With
With ADODB_SQRecordSet
.ActiveConnection = ADODB_SQConnection
.Open "SELECT * FROM dbsrc.vExperiment WHERE 1=0" 'This gives me
an
empty record set
End With
'Here is where I am stuck. I want to just insert everything
'in ADODB_ACRecordSet into ADODB_SQRecordSet
'Is there a way to do this without iterating thorugh
'every field in every row?
ExitRoutine:
If Err.Number <> 0 Then
strError = CStr(Err.Number) & vbCrLf
strError = strError & Err.Description & vbCrLf
strError = strError & Err.Source
MsgBox strError
End If
If ADODB_ACRecordSet.State <> adStateClosed Then
ADODB_ACRecordSet.Close
End If
Set ADODB_ACRecordSet = Nothing
If ADODB_SQRecordSet.State <> adStateClosed Then
ADODB_SQRecordSet.Close
End If
Set ADODB_SQRecordSet = Nothing
If ADODB_ACConnection.State <> adStateClosed Then
ADODB_ACConnection.Close
End If
Set ADODB_ACConnection = Nothing
If ADODB_SQConnection.State <> adStateClosed Then
ADODB_SQConnection.Close
End If
Set ADODB_SQConnection = Nothing
End Sub
|
