data synchronization without network 2

[RAGP]

We are trying to develop a Leads-Sales System for one of our partner companies using Visual Basic and MS access. Because it's a sales system, the users have requested a handheld verion as well. they have budget issues because they are a new company. They have "NO NETWORK" facility as of the moment. they predict having this in the 2nd or 3rd quarter of year 2005. there would be 7 nodes or regional offices with the desktop system. this is where the agents synchronize their data from the handhelds each day. the primary issue is the data completeness between nodes!!! what would be a temporary solution which would resolve this issue.

thanks

Robert Andrew G. Pangilinan
Business Analyst
Philam Systems, Inc.
AIG Group of Companies

 

[danvlas]

No network... does it refer to Internet as well?
If Internet connection is available (and permanent at least in one location), you could design a web-enabled system. But the back-end should NOT be Access. MySQL looks like a good choice: free, fast and reliable. The only thing is that stored procedures are not available (there is version 5 alpha having sps, but it's alpha...)
The front-end can be anything: web browser, VB, Access, whatever works with ODBC.
The database should reside in the location where the Internet connection is available all the time. The clients could then connect to MySQL and exchange the data through ODBC via the Internet, even by dialup.
Don't forget to use transactions to make sure that everything stays coherent...

If no permanent Internet connection is available, you would have to use e-mail and flat file attachments (Excel) from one system to another, but it's an administrative nightmare to keep the primary keys consistant, especially if everyone is ADDING records...

Access as a client is excellent. But Access as a database engine is too weak and unreliable for what you want.


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[RAGP]

thanks dan,

there is no network, internet whatsoever. That is the primary issue! I couldn't believe it myself. This is a hell of a project! I was inclining on using one of the most primitive types of data delivery which is printing a summary/report on all nodes and folding it, turning it into a paper airplane and send it off flying to the other nodes!!! Gosh!!! a module with the option of XML import/export came to mind, cause that was what i used to do with my recent company that couldn't afford leased lines as well. I'll leave the programmers up to that.

I don't wana go use discs and mail it to and fro, that would really increase data consolodation time because of the travel.

Anyways!!! Anyone with better ideas are welcome. I entertain thoughts which are obsurd, yet work.

Thanks

Robert Andrew G. Pangilinan
Business Analyst
Philam Systems, Inc.
AIG Group of Companies

 

[mrdenny]

There was a question along these lines posed several months ago. I can't find the post though, so lets see if I can remember how I had it setup.

If memery servers correctly, he had several locations around a city with no connection of any sort between SQL Servers, and he needed to replicate data between the servers.

What I recommended was that he got a beefy laptop and installed SQL Server on it. At the main office setup replication and publish the data to the laptop (with the laptop as the subscriber). Then setup the laptop as a publisher of the database that was replicated to it. Then take the laptop to each remote office, and set up the SQL Servers there as subscribers to the laptops publication. Then you simply move the laptop from one office to another, plug it into the network, and turn in on. When SQL Starts up the replication will find any partners that are available and update them.

For the "master" SQL Server at the main office setup the replication schedule to retry every 5-10 minutes, so that when the laptop comes back the "master" SQL server will see it, and they will be brought into sync.

There will be replication conflicts that will need to be addressed if all sites are doing inserting, unless you are able to handle this within the code, by manually handeling row id's and what not. But updated records will still have conflict problems that will need to be addressed.

Assuming that the offices are far away from each other (to far to drive), all the costs of shipping the laptop around it would probally be cheeper to have analog lines installed, and have all the remote offices dial into the office with the "master" sql server and replicate directly to it in the middle of the night.

As far as licensing this all goes, you would need to license the "master" SQL Server and the SQL Server on the laptop, but as long as your database stays under 2 Gigs, and you don't need to be able to use a lot of concurrent transactions at the remote offices you could get away with using MSDE which is free.

You have to use full blown sql at the Master site and at the remote site because you are setting them up as the publishers.

You will also want to make sure that the SQL database on the laptop is backed up to something other than the laptop drive each time it is shipped, so that if the disk in the laptop fails you can restore it to the state it was in when it left the last office.

Hope this helps. Let me know if you have any questions.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[bzsurf03]

mrdenny,

I enjoyed reading your post. Very interesting idea. Tough situation here. Just sounds like a nightmare. RAGP, I'm no pro myself, nor can I think of anything absurd and funny to suggest, so good luck.

 

[RiverGuy]

I would opt for XML. Using XML with .Net is fairly easy. This way, instead of the user, say downloading a copy of the Access database, and then having to copy it to the central system, importing other records, they can have an XML file on their hard drive, make changes, additions, etc.....and then post it back to the database at another time.

 

[RAGP]

mr denny,

good suggestion. although we did not consider using SQL Server for the DBASE to use with the system. And our developers are not familiar with this technology. We use DB2 when network is available and MS-Access on stand-alone machines. Although this is not a stand-alone environment, yet needs a network to run writes to DB2 through the front-ends, sad to say no network or internet whatsoever. it's really frustrating! we definitely need to purchase other DB Software and study them thoroughly as I have suggested through the years with our top management.

i'll discuss this with the project team later

thanks

bzsurf03,

thanks for giving it a thought

riverguy,

well i did try and talk the dev. team to create an import/export module for the clients to integrate it to the main system, as i told mr. denny, our people sorta lack the expertise with current development techniques because we were stuck in a Mainframe development scope up until two years ago. our R&D budget got slashed because most of our clients are banks and financial institutions which still use Mainframe technology. I can't believe this myself. Well we're getting into the Client-Server environment type of systems slowly! Well, too slow for me. I guess the issue of having "old" executives posts a problem because they usually think trditional is always best. well i always say, innovation if key. i hope they realize this sooner!!!

thanks guys...

if there are any more suggestions, i'd appreciate it

Robert Andrew G. Pangilinan
Business Analyst
Philam Systems, Inc.
AIG Group of Companies

 

[mrdenny]

RAGP,
You may want to check out MSDE. It's the free version of SQL Server. It doesn't come with all the client tools for the admins that the full blown client comes with, but it will let you get the hank of it. There are a couple of web based admin systems that you can use to admin the system much easier than you can with just the command line tool.

One is

http://sourceforge.net/projects/asp-ent-man/.

Microsoft released one, but I can't seam to find it on the Microsoft site.

You shouldn't have much problem getting used to the queries on SQL if you've been working with access. The SQL language the MS SQL uses is almost identical to the SQL that access uses.

If you have any questions I'd be happy to help.

Denny

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[bzsurf03]

It seems that the biggest problem that you face is making sure that primary keys are not used more than once, so that the data from multiple users can synchronize crrectly. I'm still assuming that Internet and any other network solutions are out of the question. Depending on the complexity of the database (how many tables you are using) and the number of end-users, would it be practical to assign key values on a daily basis for each end-user to use. For instance, values 0 - 10 for end-user #1, 11 - 20 for #2, 21 30 for #3, and so on. The next day, you could assign values 31 - 40 to end-user #1, 41 - 50 to end-user #2, and 51 - 60 to end-user #3. This solution may not work for you, but I hope it can spark new ideas.

 

[RAGP]

denny,

we are allocating a budget for SQL Server for the late 2nd Quarter of this year. I've convinced mgmt to consider this as a need for developing client-server to n-tier dbase applications. Of course not leaving out our investment in DB2. It would be an option for sure.

thanks

bzsurf03,

ok a part of the system/process contact management/leads generation looks something like this.

Leads Table/Query

Lead No. - PK - lead unique no.
Lead Name
Employee No. - FK - for referrals of leads
Employee Name
User ID - FK : for security purposes
...

Application Table/Query

Application No. - PK
Lead No. - FK
1st Appointment Date
1st Appointment Remarks
...

System Users Table/Query

User ID - PK
User Name
Password
Security Level - FK
...

This is just to give you a part of the whole picture. I mean a small part, because the system covers the whole issuance of insurance process.

Ideally, the leads gathered by bank staff are written down in paper and given to the sales agents to input into their hand held devices. Lead counters are generated there. They can also input the leads into one of the satellite office center desktops (take note, not part of a WAN and not connected to the main office DBASE). By this point, the required fields in the Leads Table/Query are populated, so a Lead No. is issued to a Lead under of course the profile of the Agent (User ID).

When the Lead is called up by the agent for appointment purposes to collect more information and come up with products suited him/her, an Application No. is generated (to allow leads to have more than one (1) application). and the rest follows.

There are seven (7) satellite offices, one (1) main office (holds the supposed DBASE) and the AS/400 running the DB2 in the insurance main office (where underwriting and other verifications are done before there is issuance of insurance policies for clients) "again no WAN not internet connecting the three, although the insurance part of the company has one, there is still no budget available for the other two to interconnect with".

The issues:

1) synchronizing data between the satellite offices. remember, agents are given handhelds and there are stand alone systems in each satellite office
2) synchronizing data between the satellite offices into the main dbase
3) supplying data found in main dbase into the hand helds (memory issue), and satellite offices.
4) send applications from main office to the AS/400 Server
5) send back the data, policy status from AS/400 Server to main office
6) make available the data from main office into the satellite offices and hand helds

Solutions so far:

1) defeat the roaming profile of each agent, meaning, take away the synchronize anywhere option and assign agents to certain satellite offices
2) use dial-up e-mail dumps of satellite offices daily to synchronize data into the main system
3) use dial-up e-mail data into the AS/400
4) e-mail back status of policies

I hope this works
any more suggestions

Robert Andrew G. Pangilinan
Business Analyst
Philam Systems, Inc.
AIG Group of Companies

 

[RAGP]

bzsurf03,

yup, one of the many obstacles of this project. I've briefed the developers on that probable issue.

thanks

Robert Andrew G. Pangilinan
Business Analyst
Philam Systems, Inc.
AIG Group of Companies