'Visual Studio 2008, working with an Access 97 table, and an SQL
Server 2008 database. I have full rights to both...

'This program runs in x86 mode, due to the lack of a 64-bit JET
OLEDB.4.0 driver

Dim dsAccess As DataSet = New DataSet
Dim dsSQL As DataSet = New DataSet

Dim tblAccess As DataTable
Dim tblSQL As DataTable

' Opening Source File

Dim strAccess As String =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\download\tt.tdf"
Dim connAccess As New OleDbConnection(strAccess)
Dim daAccess As OleDbDataAdapter = New
OleDbDataAdapter("select * from TRoom", connAccess)

'Opening Target File

Dim connSQL As SqlConnection = New
SqlConnection("Server=MYSERVER;Database=ResourceBo oking;Trusted_Connection=True")
Dim daSQL As SqlDataAdapter = New SqlDataAdapter("select *
From TRoom", conn)

connAccess.Open()

connSQL.Open()

daAccess.Fill(dsAccess, "TRoom")

daSQL.Fill(dsSQL, "TRoom")

tblSQL = dsSQL.Tables("TRoom")

tblAccess = dsAccess.Tables("TRoom")

'Copying all rows from source to target

For Each row As DataRow In tblAccess.Rows

tblSQL.ImportRow(row)

Next row

'Saving the changes

tblSQL.AcceptChanges()

da.Update(tblSQL)

conn.Close()

MsgBox("Done!")

I really want this to work. Why doesn't ImportRow actually Import the
Row?? I open the table in SQL Server 2K8 after the code has run - no
errors - but no data!

Mark

I suggest using SqlBulk copy to import from Access. I've written an example
and it's out there somewhere. If you can't find it, hit me again.

--
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
__________________________________________________ __________________________________________



"Mark B" <marximus27 (AT) hotmail (DOT) com> wrote