[RESOLVED] Windows CE 5.0 CDB and MDB files

[easymoney]

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?

[Shaggy Hiker]

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.

[easymoney]

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.

[easymoney]

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.

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

[Shaggy Hiker]

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:

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.

[easymoney]

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

Thanks.

[petevick]

Hi,
there is a SqlCE to Access synch tool at http://www.microsoft.com/downloads/details.aspx?FamilyId=B967347A-5DD0-445C-8A9F-AEA3DB9EC4BC&displaylang=en

Pete

[easymoney]

Hi,
there is a SqlCE to Access synch tool at http://www.microsoft.com/downloads/details.aspx?FamilyId=B967347A-5DD0-445C-8A9F-AEA3DB9EC4BC&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: