How to Transfer Data between databases 2

If you have sufficient rights to your connection you can upsize your Access Tables to SQL Server then run a stored procedure to append the new data to the appropriate table.

Here is some code I created awhile back to upsize my access table to SQl server


Option Compare Database
Option Explicit
Dim tblData
Const ConnectionStr As String = "PROVIDER=SQLOLEDB;DRIVER={SQL Server};SERVER=usnr0670;DATABASE=IE;uid=enekh;pwd=nhyu7;"
Function Transfer(ByVal src, ByVal dest)
CreateTable src, dest
UpsizeTable src, dest

MsgBox "DONE!"
End Function
Function CreateTable(ByVal src, ByVal dest)
Dim sql_delete As String
Dim sql_select As String
Dim sql_insert As String
Dim sql_Create As String
Dim sql_insert_part2 As String
Dim rsfield As Field
Dim rs As ADODB.Recordset
Dim rsAccess As Recordset
Dim rsinsert As ADODB.Recordset
Dim Conn As New ADODB.Connection
Dim myDB As Database
Dim a As Long
Dim i As Long
Dim counter As Long
Dim k As String

Set myDB = CurrentDb

Set rsAccess = myDB.OpenRecordset(src)

If Not rsAccess.EOF Then
rsAccess.MoveNext
With rsAccess
counter = 1
a = 0
sql_Create = "CREATE TABLE " & dest & " ("
For Each rsfield In .Fields
Dim tmp1
tmp1 = rsfield
i = VarType(tmp1)
sql_Create = sql_Create & "[" & .Fields(a).Name & "] "
Select Case i
Case vbDate
sql_Create = sql_Create & "datetime"
Case vbInteger, vbLong, vbDouble
sql_Create = sql_Create & "int"
Case vbString
sql_Create = sql_Create & "varchar(500)"
Case vbNull
sql_Create = sql_Create & "int"
Case Else
sql_Create = sql_Create & "varchar(500)"
End Select
If a = .Fields.Count - 1 Then
sql_Create = sql_Create & ") "
Else
sql_Create = sql_Create & ","
End If
a = a + 1
Next

sql_Create = "DROP TABLE " & dest & " " & sql_Create
Conn.Open ConnectionStr
Set rsinsert = Conn.Execute(sql_Create)

Conn.Close
counter = counter + 1
End With
End If
rsAccess.Close
End Function
Function UpsizeTable(ByVal src, ByVal dest)
Dim sql_delete As String
Dim sql_select As String
Dim sql_insert As String
Dim sql_Create As String
Dim sql_insert_part2 As String
Dim rsfield As Field
Dim rs As ADODB.Recordset
Dim rsAccess As Recordset
Dim rsinsert As ADODB.Recordset
Dim Conn As New ADODB.Connection
Dim myDB As Database
Dim a As Long
Dim i As Long
Dim counter As Long
Dim k As String

Set myDB = CurrentDb


Set rsAccess = myDB.OpenRecordset(src)

If Not rsAccess.EOF Then
With rsAccess
counter = 1

Do
a = 0
sql_insert = "Insert into " & dest & " ("
sql_insert_part2 = " values ('"
For Each rsfield In .Fields
sql_insert = sql_insert & "[" & .Fields(a).Name & "]"
If Not IsNull(rsfield) Then
sql_insert_part2 = sql_insert_part2 & Replace(rsfield, "'", "")
Else
sql_insert_part2 = sql_insert_part2 & rsfield
End If
If a = .Fields.Count - 1 Then
sql_insert = sql_insert & ") "
sql_insert_part2 = sql_insert_part2 & "') "
Else
sql_insert = sql_insert & ","
sql_insert_part2 = sql_insert_part2 & "','"
End If
a = a + 1
Next

sql_insert = sql_insert & sql_insert_part2
Conn.Open ConnectionStr
Set rsinsert = Conn.Execute(sql_insert)

rsAccess.MoveNext

Conn.Close
counter = counter + 1
Loop Until rsAccess.EOF
End With
End If
rsAccess.Close
Exit Function
ERR_DESC:
MsgBox Err.Description
End Function

Hey also Create a form and u can use the above functions to append data from Source to Destination

Private Sub cmdTransfer_Click()
Transfer Me.txtSrc, Me.txtDest
End Sub

SQL Server has a function called OpenRowSet that will handle data across platforms. You should be able to use an insert into query. Insert into sql server table select form access table. Here is an example of a select query using openrowset.


Public Function rowset() '- function in standard module
'-- set reference to ADO library
'-- Microsoft ActiveX data objects 2.6 library also needed for ADO

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

Dim sqlString As String
sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
'--- Define the location and database name
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If
End Function

PeasNCarrots - Thank you for your suggestion. It's a good idea, but for me in the wrong direction. This append is done daily by users, not me. Thus, They do not have the rights. Thank you very much for the suggestion - I never would have thought of that approach, which may not be right for me now, it may be the right approach later.

cmmrfrds - That is exactly what i was looking for. Thank you very much. I haven't tried it yet, but it looks like it makes sense!

Randall Vollen
National City Bank Corp.