EXPORT ACCESS TO SQL DATABASE RECORD BY RECORD...

[gssitaly via AccessMonster.com]

I use this code to update a sql database from excel to sql...
Now i would want to arange this code to export an Access table into
sql databse...

Sub ADOExcelToSQLServer()
Dim Cn As ADODB.Connection
Dim ServerName As String
Dim DatabaseName As String
Dim TableName As String
Dim UserID As String
Dim Password As String
Dim rs As ADODB.Recordset
Dim NoOfFields As Integer
Dim Row As String
Dim ws As Worksheet
Set rs = New ADODB.Recordset


ServerName = "USER-E114319F02"
DatabaseName = "northwind"
TableName = "Employees"
UserID = ""
Password = ""
Row = 1

Set ws = ThisWorkbook.Worksheets("FOGLIO1")

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database="
& DatabaseName & _
";Uid=" & UserID & ";Pwd=" & Password & ";"

rs.Open TableName, Cn, adOpenKeyset, adLockOptimistic

While Not ws.Range("A" + Row) = ""

rs.AddNew

rs![LASTName] = ws.Range("A" + Row).Value
rs![FIRSTName] = ws.Range("B" + Row).Value
rs![Title] = ws.Range("C" + Row).Value
rs.UpdateBatch

Row = Row + 1

Wend
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub
'''''''''''''''''''''''''''''''''''''''''
In effect insted to use column and cell from sheet use column and
record from access table...
Important for me is to make a code write record by record...similar
the block in code:

While Not ws.Range("A" + Row) = ""

rs.AddNew

rs![LASTName] = record of access table
rs![FIRSTName] = record of access table
rs![Title] = record of access table
rs.UpdateBatch

Row = Row + 1

Wend

 

[seeCoolGuy]

best way would be to open up a recordset in Access, and loop through it
while you populate the sql recordset, that is if you have to do it in
access. the other way to do this import would be to setup a job on the
sql side using DTS and importing your data in that direction...(ymmv)