EXPORT ACCESS TO SQL DATABASE RECORD BY RECORD...

[sal21 sal21]

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

*** Sent via Developersdex http://www.developersdex.com ***

[pietlinden]

Wait, are you importing Excel data to SQL Server, or to Acccess
or...??

If so, why would you want to import a record at a time, if all the
records are going into the same table? Why not use
TransferSpreadsheet?? Unless you need to do complex data checking on
each record or something?

Maybe you should explain further... why will this not work for you? Or
do you just like making your own life difficult?