using MS Access for Pocket PC

[shomoodro]

Hi,

I'm new to the mobile era. I have an application running on desktops. I'm using VS 2003 and Access database. I want to make my application work on pocket pc. Till I can communicate with my desktop application through tcp/ip, but can't find a way to connect to my ACCESS database. In my app I used either oledb or adodb.recordset to connect to DB. but here i found none of them. I need suggestion from you how should I move forward. I have several things in mind -

1. find a way out to use existing Access DB
2. Use TCP/IP request to the server and let server do all the tasks
3. Convert the whole app with DB in sql server, so that I can use SQLServerCE.

for 1, as far I found, there is a tool called ActiveSync, which synchronize the dbs in desktop and pocket pc (cdb to mdb and viceversa). Won't it be a slow process. I mean everytime i made a change in the db I have to synchronize the whole db in all clients.

If I can't get through 1 I'll use 2. But stuck with the thought that it might be better to move to sql server now before I have to. Before that I need to know if i use sqlserverce will it be possible to connect to sqlserver 2000 (on desktop) directly or will I be required to go with the synchronization process again.

Thank you in advance

shomoodro

[Shaggy Hiker]

I'll tell you how I have something similar to that, which may give you some ideas:

I have a program that runs on a laptop (it is always in the field, so no point in even pretending it's on a desktop), and there's a version that runs on PDA's. The PDA's are certainly not in constant connection with the laptop. The database on the PDA's are not truly relational, because it wasn't very efficient for them to be so in this case. When the PDA's are placed in the cradles, I can press a single button on the PDA, and all the lookup tables from the laptop are packed into XML on the laptop, and sent to the PDA to update any changes in those tables. Then the data from the PDA is packed into XML and sent back to the laptop.

On the laptop, the TCPListener is running in a separate thread in the background, so all the XML transfer is behind the scenes on the laptop.

On the PDA's, the database is all SQLServerCE, and is created on the fly so that the entire database can be simply re-created as need arises (it happens). Thus the user can press a button, and, after a warning, can start with a fresh and empty database. Then they can press a button (with the PDA in the cradle), and the lookup tables in the PDA can be updated from the master Access DB on the laptop.

Is this the best solution? Probably not in general, but it might be in this case. A wireless connection to the laptop is out of the question because of the location, so the PDA's have to be docked. Data can only be transfered at that time. Data could be sent in both directions (it usually is), but in this case, all the meaningful data is only going from the PDA to the laptop. Changing this so that you are synching with an existing DB doesn't seem like it would be very difficult, but it could be slow if a large amount of data is being sent over the link. Of course, if you were synching to SQL Server, much of this is done automatically.

As for your specific situation, I would guess that synching Access with the PDA will prove to be slow. Adding a field on the PDA so that you only have to send down those records that have changed would speed up the PDA->Server option, because less would move. However, I can't see any general solution that would allow you to decide which lines have changed so that you can limit the amount of data being sent Server->PDA......unless you keep an additional table tracking which records have been sent to each possible PDA, but that has all kinds of extra baggage associated with it.

[Strider]

Another option would be to use a Webservice on your Server/PC which will connect to the Access database.

You could then call this Webservice over activesync or via wireless(if the pda is wifi) and pull that data from the access database to the sqlce database on the pda.

You could send back changes again by calling a webservice to push data back into access

[shomoodro]

Hi Shaggy and Strider

Thanks a lot. I think the XML idea will suit me best. I have one table which contains item descriptions and prices and the pda will need it frequently. I can let the users update that table when they press an update button. Then I can use that while preparing the order in PDA. Once the order is confirmed, I can send it to the server and the server will do the rest.

hope that will work. thanks again

shomoodro

[Shaggy Hiker]

I can only assume that you are using wifi or some such.

[shomoodro]

yes, you're right and I already made the connection and start talking.

shomoodro

[Pzykotik]

Shaggy, which program are you using to do all that sync stuff? Only it sounds exactly like what I'm going to need! I'm going to have a database on my server (we're using mumps or something similar that spits out XML code) and I'm going to need to update the PDAs with this new data when the user presses a button... I've been looking around the net as to how I'm going to do it and your solution looks the most accurate so far.

Thanks in advance,

Pzykotik

[Shaggy Hiker]

I wrote all that stuff in VB.NET 2003. It consists of two classes, and that was over a year ago. I think I posted the classes on here at some point. You're welcome to them. They seemed to work pretty well, but there may have been a problem if I tried to send MANY rows of data, and there were a few things I never really liked about them.