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?
Printable View
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.
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 ????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
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
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.Quote:
Originally Posted by petevick
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: