[RESOLVED] Windows CE 5.0 CDB and MDB files

I am looking for more info or an example of how to insert or delete data into CDB (converted from MDB) files using ADOCE connection.

Is this possible?

What version of language are you using? With .NET, the DB is SQLServerCE, and dealing with that is fairly straightforward, as there is a whole namespace with a suite of tools for working with such a database. I don't believe you can use the classes in that namespace to work with other types of databases, and I don't think that ODBC or OLEDB namespaces are available for the .NET Compact Framework.

Yes, I am using .NET.

I need the ability to store data entered into a handheld, and then import it onto a PC based app that uses MS Access as a backend. Basically I need 2 tables with 8 fields each.

We are in the process of converting the app to run Sql Server, but need to import data now.

Is it possible to use SqlCe, then import the data to an Access db?


hxxp://blogs.msdn.com/sqlmobile/archive/2006/06/01/612768.aspx

I know it is a backward direction, but looking for a temp solution. Thanks.

Okay, I have been doing some reading. ActiveSync dropped support for mdb file conversions, hence my problem.

So I have to use SqlCe on the handheld, then ActiveSync the Db to the desktop PC. Then I need to convert the Sql back to MDb format, then import using the application. I assume this process will be streamlined once desktop app is upgraded to Sql server.


Maybe this is the fix that was claimed Microsoft promised...

Not sure about rules regarding posting links, hope I don't get in trouble.

Code:

---

Microsoft SQL Server 2005 Compact Edition Access Database Synchronizer

hxxp://www.microsoft.com/downloads/details.aspx?FamilyID=b967347a-5dd0-445c-8a9f-aea3db9ec4bc&DisplayLang=en

---

A side note, ..... what is with all the differnet versions of Ce and Windows Mobile... Ce 3.0, Ce5.0, Mobile 5.0 and now Mobile 6 ????

I have done what you are trying to do, though I'm not entirely sure whether I have any code that would be of any value to you. The basic solution you need is to take the SQLServerCE, and dump it into XML, transmit the XML, and load it into Access. This works, and really takes VERY little code. The part that I have never absolutely solved was the transfer part. I used TCP, and had no experience with it at all. While what I had was working, it may have only worked when there were only a few records. I don't remember whether I figured out what I was doing wrong, or just abandoned the project.

Here's an example of a function I used to create the XML string for sending:

vb Code:

Private Function MakeOutput(ByVal typ As Integer) As String
        Dim lDadapt As System.Data.SqlServerCe.SqlCeDataAdapter
        Dim lDset As New System.Data.DataSet
        Dim st1 As String
        Dim cmd As System.Data.SqlServerCe.SqlCeCommand
 
        Try
            cmd = dbM.GetCommand
        Catch ex As Exception
            MsgBox("Database is not valid.")
            MakeOutput = ""
            Exit Function
        End Try
 
        Try
            'For either one, the ID must be included.
            cmd.CommandText = "SELECT Ident FROM IDTable WHERE IDId = 1"
            lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
            lDadapt.Fill(lDset, "Identifier")
            lDadapt.Dispose()
 
            If typ = 1 Then
                cmd.CommandText = "SELECT * FROM EffortTable WHERE Downloaded < 2"
                'Get the tables and put them in the dataset.
                lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
                lDadapt.Fill(lDset, "EffortTable")
                lDadapt.Dispose()
                cmd.CommandText = "SELECT * FROM FishTable WHERE Downloaded < 2 "
                lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
                lDadapt.Fill(lDset, "FishTable")
                lDadapt.Dispose()
                cmd.CommandText = "SELECT * FROM MaxAnglerNumber WHERE Downloaded < 2 "
                lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
                lDadapt.Fill(lDset, "MaxAnglerNumber")
 
                If lDset.Tables("EffortTable").Rows.Count = 0 And lDset.Tables("FishTable").Rows.Count = 0 Then
                    Windows.Forms.MessageBox.Show("There is no new data to be sent, so nothing will be sent.")
                    st1 = ""
                Else
                    'This may not be the best way to do this, but get a string of the XML.
                    st1 = lDset.GetXml()
                End If
            Else
                    cmd.CommandText = "SELECT * FROM EffortTable"
                    'Get the tables and put them in the dataset.
                    lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
                    lDadapt.Fill(lDset, "EffortTable")
                    lDadapt.Dispose()
                    cmd.CommandText = "SELECT * FROM FishTable"
                    lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
                    lDadapt.Fill(lDset, "FishTable")
                    lDadapt.Dispose()
                    cmd.CommandText = "SELECT * FROM MaxAnglerNumber"
                    lDadapt = New System.Data.SqlServerCe.SqlCeDataAdapter(cmd)
                    lDadapt.Fill(lDset, "MaxAnglerNumber")
 
                    'This may not be the best way to do this, but get a string of the XML.
                    st1 = lDset.GetXml()
                End If
                MakeOutput = st1
        Catch ex As System.Data.SqlServerCe.SqlCeException
            Windows.Forms.MessageBox.Show("Error making output file." & Chr(10) & ex.Message)
            MakeOutput = "FAIL"
        End Try
    End Function
 
    'This sets the downloaded bit for all of the data in the tables.
    Private Sub UpdateTables()
        Dim cmd As System.Data.SqlServerCe.SqlCeCommand
 
        Try
            cmd = dbM.GetCommand
        Catch ex As Exception
            MsgBox("Updating Downloaded failed for unknown reason.")
        End Try
 
        Try
            cmd.CommandText = "UPDATE FishTable SET Downloaded = 2"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "UPDATE EffortTable SET Downloaded = 2"
            cmd.ExecuteNonQuery()
            cmd.CommandText = "UPDATE MaxAnglerNumber SET Downloaded = 2"
            cmd.ExecuteNonQuery()
        Catch ex As System.Data.SqlServerCe.SqlCeException
            MsgBox("Updating download failed because of: " & ex.Message)
            cmd.Dispose()
        End Try
    End Sub
 
    Private Function GetTables(ByVal stXML As String) As Boolean
        Dim retVal As Integer
        Dim dSet As New System.Data.DataSet
        Dim sRead As System.IO.StringReader
        Dim xmlTxtR As System.Xml.XmlTextReader
        Dim cmd As System.Data.SqlServerCe.SqlCeCommand
        Dim dTable As System.Data.DataTable
        Dim rw As System.Data.DataRow
 
 
        'This should take what was passed in and turn it into a string.
        sRead = New System.IO.StringReader(stXML)
 
        xmlTxtR = New System.Xml.XmlTextReader(sRead)
        dSet.ReadXml(xmlTxtR, XmlReadMode.InferSchema)
 
        'Now we need to kill off anything that is in the shift or location table, because if 
        'there is anything in there that is not in the incoming thing, you are basically ****ed.
 
        'However, this could also mean that you should clear the data from the database, but that
        'is probably NOT a very good idea, because actual data is lost. Residual data associated
        'with table entries that no longer exist are a problem on the downstream side, not the 
        'upstream side.
        Try
            cmd = dbM.GetCommand
            cmd.CommandText = ("DELETE FROM ShiftTable")
            cmd.ExecuteNonQuery()
            cmd.CommandText = ("DELETE FROM LocationTable")
            cmd.ExecuteNonQuery()
        Catch ex As System.Data.SqlServerCe.SqlCeException
            MsgBox(ex.Message)
        End Try
 
        'Now the data from the dataset must be shifted to the table.
        dTable = dSet.Tables("ShiftTable")
        For Each rw In dTable.Rows
            cmd.CommandText = ("INSERT INTO ShiftTable (ShiftName) VALUES(?)")
            cmd.Parameters.Add("ShiftName", rw.Item("ShiftName"))
            cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
        Next
 
        dTable = dSet.Tables("LocationTable")
        For Each rw In dTable.Rows
            cmd.CommandText = ("INSERT INTO LocationTable (LocationName) VALUES(?)")
            cmd.Parameters.Add("LocationName", rw.Item("LocationName"))
            cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
        Next
 
        cmd.Dispose()
        sRead.Close()
        xmlTxtR.Close()
        GetTables = True
    End Function

Oops, copied too much. Ok, it's two functions, one reads, one writes.

Looks pretty straight forward, I will play with this code and let you know what happens.

Thanks.

Hi,
there is a SqlCE to Access synch tool at http://www.microsoft.com/downloads/d...displaylang=en

Pete

Quote:

---

Originally Posted by petevick

Hi,
there is a SqlCE to Access synch tool at http://www.microsoft.com/downloads/d...displaylang=en

Pete

---

Yes, I finally found the promised tool as I posted this link above... but thanks for the confirmation... still a little unsure about the process.

I guess I will mark this thread resolved as I am convinced that I need SQLServerCE and can import the data to an Access Db either via the Access Sync Tool, XML, or if all else fails CSV format....

Now I have to learn SQLServerCE. I will make another thread detailing the process. I still have lots of questions, but this one is resolved.

Thanks everyone :wave: