TRANSFER DATA FROM ACCESS TO SQL THROUGH XML

emi_sastra
emi_sastra used Ask the Experts™
on
Hi All,

I want to transfer data from access to sql.

What I do are :

1. Read Access data and save it to xml.

 SELECT DISTINCT FingerPrintID, DateLog, TimeLog, 'ADMINIST', '16/10/2010 22:58:35', 'ADMINIST', '16/10/2010 22:58:35' FROM PersonalLog WHERE DateLog BETWEEN #2010/10/01# AND #2010/10/15#

2. Read xml into datatable and write to sql database.

I get exception :

Incorrect syntax near 'COLLATE'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

Sample xml data generated:

<Table>
  <FingerPrintID>5</FingerPrintID>
  <DateLog>2010-10-01T00:00:00+07:00</DateLog>
  <TimeLog>07:19</TimeLog>
  <Expr1003>ADMINIST</Expr1003>
  <Expr1004>16/10/2010 22:58:35</Expr1004>
  <Expr1005>ADMINIST</Expr1005>
  <Expr1006>16/10/2010 22:58:35</Expr1006>
  </Table>


What's wrong with my code ?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is there a particular reason to transfer via XML?

Commented:
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

Author

Commented:
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.



Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Author

Commented:
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?

Author

Commented:
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

Open in new window

Author

Commented:
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

 

Author

Commented:
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

Open in new window

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

Author

Commented:
strSQLCommand = sdbSQLCommand.ToString
It is string builder.

Thank you.
OK, I should have seen that from the code, I was tired when I looked at it.

Assuming that you've set-up dbo_MyHostTable as a Linked table in Access connected to your SQL Server table, the attached code will insert rows selected from the Access table.

Hope this helps
        sdbSQLCommand.Append("INSERT INTO dbo_MyHostTable ( FingerprintID, DateLog, TimeLog ) ")
        sdbSQLCommand.Append("SELECT DISTINCT")
        sdbSQLCommand.Append(" FingerPrintID, 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 accessConnection As New OleDbConnection(strDataAbsensiConnection)
            accessConnection.Open()
            Dim cmd = New OleDbCommand
            With cmd
                .Connection = accessConnection
                .CommandType = CommandType.Text
                .CommandText = strSQLCommand
                .ExecuteNonQuery()
            End With
        Catch ex As Exception
            Stop
        Finally
            accessConnection.Close()
        End Try

Open in new window

Author

Commented:
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

Open in new window

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial