TRANSFER DATA FROM ACCESS TO SQL THROUGH XML

Is there a particular reason to transfer via XML?

Are you on SQL2000? You need 2005 for XML. If it's a 2000 database you restored on 2005 you may need to change the compatibility. EXEC sp_dbcmptlevel {{DatabaseName}}, 90

Hi MikeToole,

>>Is there a particular reason to transfer via XML?
Transfer from Access database to MS SQL 2005. Because I don't want to use open rowset, it is complicated.

Thank you.

Hi rmm2001,

Please see the above post to Mike.

Thank you.

There are ways of updating SQL Server tables  directly from Access by executing Access SQL. That would then just need VB.Net code to trigger the execution of the Access SQL. Is this an approach that would fit your situation?

Any solution that is easier to implement are welcome.

Thank you.

Access has the concept of 'Linked Tables' by which a table in another database can be read/updated by SQL running in Access.
One solution to your problem is therefore this:

1) Add a linked table in Access pointing to the SQL Server table you want to update - in the example I created a table called MyHostTable in sql server and it was linked into Access with the name dbo_MyHostTable
2) Create an Access query to Insert rows to dbo_MyHostTable from a Select from PersonalLog - I called this Query 'InsertServertable' (the attached code shows the sql in the query I used as a test, you would obviously substitute your own)
3) Run the query from vb.net via an ADO.Net OLEDB connection  
 
- Substitute the path to your own Access db in the definition of dbPath
- The connection string in the sample is set for the ACCDB  type of Access database - use the alternate provider string if the target is in the MDB format

Access Query 'InsertServertable'

INSERT INTO dbo_MyHostTable ( FingerprintID, DateLog, TimeLog )
SELECT PersonalLog.FingerprintID, PersonalLog.DateLog, PersonalLog.TimeLog
FROM PersonalLog;

' Code to execute from VB.Net
Imports System.Data.OleDb
Module ADO
    Sub RunAccessQuery()
        Dim dbPath = My.Computer.FileSystem.SpecialDirectories.MyDocuments + "\Development\testCode\LoadToSQLServer.accdb"
        'Dim ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + dbPath
        Dim ConnString = "Provider=Microsoft.ACE.OLEDB.12.0;  Persist Security Info=False; Data source=" + dbPath
        Dim conn As New System.Data.OleDb.OleDbConnection(ConnString)

        Try
            conn.Open()
            Dim cmd = New OleDbCommand
            With cmd
                .Connection = conn
                .CommandType = CommandType.StoredProcedure
                .CommandText = "InsertServertable"
                .ExecuteNonQuery()
            End With



        Catch ex As Exception
            Console.WriteLine("Failed to connect to data source")
            Console.Read()
        Finally
            conn.Close()
        End Try

    End Sub
End Module

Select allOpen in new window

Hi MikeToole,

Access Query 'InsertServertable'

INSERT INTO dbo_MyHostTable ( FingerprintID, DateLog, TimeLog )
SELECT PersonalLog.FingerprintID, PersonalLog.DateLog, PersonalLog.TimeLog
FROM PersonalLog;

Is this code place at Access Database ? Should I call it every day, because it is attendance data?

How to link MyHostTable to dbo_MyHostTable ?

Thank you.

Access allows you to store SQL statements in Query objects, so, yes, the SQL is 'code' in the Access database.
I created it that way because it's easy to set-up and test - Access provides a visual builder for Query objects.
If you don't feel comfortable working within Access, you con supply the SQL string directly to an oledb command in VB.Net - just change the CommandType to CommandType.Text and assign the SQL string to CommandText.

There is an External Data wizard in Access that will guide you through the process of creating the Linked table

Should you call it every day? - Well, if you want the data to be added every day, then yes.

A completely different, and arguably better, approach would be to use SQL Server Integration Services to import the data from Access to SQL. Define the import process in SSIS and schedule it to run every day. That way everything is done in SQL Server, there's no need to do anything in Access and the dot.net code becomes redundant.

Hope this helps,
Mike

 

Please below code, could I add something to make it transfer data to sql table?

Thank you.

   Private Sub Isi_Data_Mentah_From_Access(ByVal strEmpCode As String)

              sdbSQLCommand.Length = 0

             sdbSQLCommand.Append("SELECT DISTINCT")
        sdbSQLCommand.Append(" FingerPrintID AS ID, DateLog, TimeLog")
        sdbSQLCommand.Append(" FROM PersonalLog")
        sdbSQLCommand.Append(" WHERE FingerPrintID = " & strEmpCode.Trim & " AND DateLog BETWEEN #" & Format(dteFromDate, "yyyy/MM/dd") & "# AND #" & Format(dteToDate, "yyyy/MM/dd") & "# ")
        sdbSQLCommand.Append(" ORDER By DateLog, TimeLog")

        strSQLCommand = sdbSQLCommand.ToString

        Try

            Dim accessDataSet As New DataSet()
            Dim accessConnection As New OleDbConnection(strDataAbsensiConnection)
            Dim accessAdapter As New OleDbDataAdapter(strSQLCommand, accessConnection)

            accessAdapter.Fill(accessDataSet, "Logs")
            dtLogsDetail = accessDataSet.Tables("Logs")

         Catch ex As Exception
      
        End Try
 

    End Sub

Select allOpen in new window

What is the Type of sdbSQLCommand, is it an OleDbCommand?

strSQLCommand = sdbSQLCommand.ToString
It is string builder.

Thank you.

Could I not use link table to achieve it because :

1. I don't know how to do it.
2. It is more convinient using code rather set it using wizard or manually.

Thank you.

The Linked table creation is a one-time action, it doesn't need to be performed for every transfer.
There are methods of creating the link in code - it depends on whether the target access database is in the mdb or accdb format.
However, I'd recommend to just open the db in Access and create the linked table - it's a five minute job. Which version of Access are you using?

The attached code will create a linked table via code. It needs to run only once.

Imports Microsoft.Office.Interop
Module ADO
    Dim dbPath = My.Computer.FileSystem.SpecialDirectories.MyDocuments + "\Development\testCode\LoadToSQLServer.accdb"

    Sub AddLinkedTable()
        Const Conn = "ODBC; Driver={SQL Server}; Trusted_Connection=Yes; Server=MyServer\MySQLInstance; Database=MyDatabase;"
        Const AccessTableName = "dbo_MyHostTable"
        Const ServertTableName = "MyHostTable"
        ' Required COM reference to Microsoft Access to be added to project 
        Dim app As New Access.Application
        Dim db As Access.Dao.Database
        Dim tdf As Access.Dao.TableDef
        Try
            app.OpenCurrentDatabase(dbPath)
            db = app.CurrentDb
            Try
                db.TableDefs.Delete(AccessTableName)
            Catch
            End Try
            tdf = db.CreateTableDef(AccessTableName)
            tdf.SourceTableName = ServertTableName
            tdf.Connect = Conn
            db.TableDefs.Append(tdf)
            db.Close()
        Catch ex As Exception
            Stop
        Finally
            If Not tdf Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(tdf)
                tdf = Nothing
            End If
            If Not db Is Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(db)
                db = Nothing
            End If
            If Not app Is Nothing Then
                app.Application.Quit(Access.AcQuitOption.acQuitSaveAll)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
                app = Nothing
            End If
        End Try
    End Sub

Select allOpen in new window

Hi MikeToole,

I am sorry, that I have lost my access table, thus I don't  know the version you asked.

I will try the link table code later.

Thank you very much for your help.