Offline application for reporting and collect data

[Senthil]

Hi All
We are having a VB application on SQL. But we need to collect
information from persons who will be offline to verify data and insert
new data. Generally they will be entering the data in Excel spread
sheets which can be uploaded to the database using the application
after some validations.

But rather than Excel I was looking at Infopath with Access as the
database, to create validation rules and collect data offline that can
be submitted at a later stage when the user is back online. But the
reporting tool is not serving the purpose.
Rather Can i build a VB.Net application that comes with Access data
base embedded in it in the installer so that even when the user is
offline we can get him the reports and enable him to update or enter
new data through the grids?? And later we can easily gather the data to
SQL by just importing using DTS.
Or is there any other better approach? Please advice.

Thanks
Senthil

[Ken Tucker [MVP]]

Hi,

Take a look at the sql compact edition now in release canidate.
Sql Compact edition has some nice features for sync with sql server. It
should be out by the end of the year.

http://blogs.msdn.com/stevelasker/ar...-released.aspx

http://blogs.msdn.com/stevelasker/ar...whereInfo.aspx

http://blogs.msdn.com/stevelasker/ar...eSnipetts.aspx

Ken
-----------------------
"Senthil" <se************@gmail.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...

Hi All
We are having a VB application on SQL. But we need to collect
information from persons who will be offline to verify data and insert
new data. Generally they will be entering the data in Excel spread
sheets which can be uploaded to the database using the application
after some validations.

But rather than Excel I was looking at Infopath with Access as the
database, to create validation rules and collect data offline that can
be submitted at a later stage when the user is back online. But the
reporting tool is not serving the purpose.
Rather Can i build a VB.Net application that comes with Access data
base embedded in it in the installer so that even when the user is
offline we can get him the reports and enable him to update or enter
new data through the grids?? And later we can easily gather the data to
SQL by just importing using DTS.
Or is there any other better approach? Please advice.

Thanks
Senthil

[Senthil]

Hi Ken/All,
Thanks for the update man. That is very interesting stuff. But
what I plan is giving an installer to the client that will install the
Application and the database needed for it(incase of MDB i can load the
MDB with tables). But as far as I have worked with SQL, first we need
to install SQL server and restore the database backup in the system. So
the user has to install the SQL compact and then has to do a backup of
the database! Can I do this also in the installer?? (I preferred ACCESS
just to short cut this procedure as I cannot force the user to install
a lot)

Thanks and Regards
Senthil
Ken Tucker [MVP] wrote:

Hi,

Take a look at the sql compact edition now in release canidate.
Sql Compact edition has some nice features for sync with sql server. It
should be out by the end of the year.

http://blogs.msdn.com/stevelasker/ar...-released.aspx

http://blogs.msdn.com/stevelasker/ar...whereInfo.aspx

http://blogs.msdn.com/stevelasker/ar...eSnipetts.aspx

Ken
-----------------------
"Senthil" <se************@gmail.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...

Hi All
We are having a VB application on SQL. But we need to collect
information from persons who will be offline to verify data and insert
new data. Generally they will be entering the data in Excel spread
sheets which can be uploaded to the database using the application
after some validations.

But rather than Excel I was looking at Infopath with Access as the
database, to create validation rules and collect data offline that can
be submitted at a later stage when the user is back online. But the
reporting tool is not serving the purpose.
Rather Can i build a VB.Net application that comes with Access data
base embedded in it in the installer so that even when the user is
offline we can get him the reports and enable him to update or enter
new data through the grids?? And later we can easily gather the data to
SQL by just importing using DTS.
Or is there any other better approach? Please advice.

Thanks
Senthil

[MSJ3990]

Ken,
I have written a similar application. I use a vb 2005 front end with
an embedded access database. It is distributed to several customers
for offline data collection. Then they just email me the database, and
I import thier inputs into my master copy. You could also import these
into SQL as well. If you want more info, contact me at ms*****@cox.net
Michael
Senthil wrote:

Hi Ken/All,
Thanks for the update man. That is very interesting stuff. But
what I plan is giving an installer to the client that will install the
Application and the database needed for it(incase of MDB i can load the
MDB with tables). But as far as I have worked with SQL, first we need
to install SQL server and restore the database backup in the system. So
the user has to install the SQL compact and then has to do a backup of
the database! Can I do this also in the installer?? (I preferred ACCESS
just to short cut this procedure as I cannot force the user to install
a lot)

Thanks and Regards
Senthil
Ken Tucker [MVP] wrote:

Hi,

Take a look at the sql compact edition now in release canidate.
Sql Compact edition has some nice features for sync with sql server. It
should be out by the end of the year.

http://blogs.msdn.com/stevelasker/ar...-released.aspx

http://blogs.msdn.com/stevelasker/ar...whereInfo.aspx

http://blogs.msdn.com/stevelasker/ar...eSnipetts.aspx

Ken
-----------------------
"Senthil" <se************@gmail.comwrote in message
news:11**********************@j44g2000cwa.googlegr oups.com...

Hi All
We are having a VB application on SQL. But we need to collect
information from persons who will be offline to verify data and insert
new data. Generally they will be entering the data in Excel spread
sheets which can be uploaded to the database using the application
after some validations.
>
But rather than Excel I was looking at Infopath with Access as the
database, to create validation rules and collect data offline that can
be submitted at a later stage when the user is back online. But the
reporting tool is not serving the purpose.
Rather Can i build a VB.Net application that comes with Access data
base embedded in it in the installer so that even when the user is
offline we can get him the reports and enable him to update or enter
new data through the grids?? And later we can easily gather the data to
SQL by just importing using DTS.
Or is there any other better approach? Please advice.
>
Thanks
Senthil
>

[jeff]

Senthil

I have done exactly what you are asking about ... for a construction company
.... about 4 years ago ... and is still in production!

Tips:

Build one user interface that points to either you SQL Server database (when
connected) or to the MS Access Database file when not connected...build the
necessary functionality in your DAL to deal with difference between the two
database technologies (dates inclosed with # for access and ' for sql
server)...

Build custom synchronization routines to deal with 'data exchange /
transfer' ... this will allow you to run 'out-of-sync' versions between
remote users and the office ... the synchronization routine will adjust and
alert the user accordingly when uploading data from XML files (xml files to
be used for data exchange). This gives you a lot more control over data
exchange and makes it really easy to accomodate other data sources for
importing.

Why 'out-of-sync' version! Because there WILL be times when you need to get
an update out ... but you can not feasibly locate (access) all the remote
users...however, these remote users will still be sending you data ...
Example .... situation ... remote construction crew using satelite phones
for communication ... you do not want to be sending them a 20meg file that
takes 2 hours to download (if the connection is not dropped) ... @ 2 bucks a
minute ... if the update has nothing to do with them! By building version
control in you exchange process, you can easily deal with this! I can make
changes to the database schema and not worry about the exchange process ....
the exchange manager I build will automatically deal with these changes on
export and import.

Basically, user starts application ... program checks for connectivity to
network ... is it there ...
yes ... update interface ... update local MDB ... prompt user if they want
to connect to their local database or the server ...
no ... connect the user to their local MDB file...work as normal...

User wants to 'exchange' data - synchronize it ... push it to the server...
....start exchange program .... produce identified xml documents...now ...the
user either...
- emails files to a company email account used to manage incoming files ...
program on the server that checks for and processes any new incoming data
files...
- saves the files to a know folder for immediate processing...

....for pulling data from the server ... user either...
- uses the program to build a 'request queue' (i.e. all new projects in
Alberta for Client XYZ, Updated Employee List, Updated Equipment List and
Maintenance Work ORders for Equipment Unit 123, Unit 12344, Unit 123344) ...
Email sent to the manage email account ... email is processed ... program
sends back a compressed XML file with the data....user gets email ... loads
data done...

- if user is connected to the network ... click a button ...update database!

Hope this gives you some ideas ... for more specifics ... jus task.

Jeff.

either email them to the office or save the


"Senthil" <se************@gmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...

Hi Ken/All,
Thanks for the update man. That is very interesting stuff. But
what I plan is giving an installer to the client that will install the
Application and the database needed for it(incase of MDB i can load the
MDB with tables). But as far as I have worked with SQL, first we need
to install SQL server and restore the database backup in the system. So
the user has to install the SQL compact and then has to do a backup of
the database! Can I do this also in the installer?? (I preferred ACCESS
just to short cut this procedure as I cannot force the user to install
a lot)

Thanks and Regards
Senthil
Ken Tucker [MVP] wrote:

>Hi,

Take a look at the sql compact edition now in release
canidate.
Sql Compact edition has some nice features for sync with sql server. It
should be out by the end of the year.

http://blogs.msdn.com/stevelasker/ar...-released.aspx

http://blogs.msdn.com/stevelasker/ar...whereInfo.aspx

http://blogs.msdn.com/stevelasker/ar...eSnipetts.aspx

Ken
-----------------------
"Senthil" <se************@gmail.comwrote in message
news:11**********************@j44g2000cwa.googleg roups.com...

Hi All
We are having a VB application on SQL. But we need to collect
information from persons who will be offline to verify data and insert
new data. Generally they will be entering the data in Excel spread
sheets which can be uploaded to the database using the application
after some validations.

But rather than Excel I was looking at Infopath with Access as the
database, to create validation rules and collect data offline that can
be submitted at a later stage when the user is back online. But the
reporting tool is not serving the purpose.
Rather Can i build a VB.Net application that comes with Access data
base embedded in it in the installer so that even when the user is
offline we can get him the reports and enable him to update or enter
new data through the grids?? And later we can easily gather the data to
SQL by just importing using DTS.
Or is there any other better approach? Please advice.

Thanks
Senthil

[Spam Catcher]

"Senthil" <se************@gmail.comwrote in news:1164977472.565260.149500
@l12g2000cwl.googlegroups.com:

But as far as I have worked with SQL, first we need
to install SQL server and restore the database backup in the system. So
the user has to install the SQL compact and then has to do a backup of
the database! Can I do this also in the installer??

You can run a SQL script to build the tables, or ship the database with the
installer.

[Senthil]

Hi All

@Jeff That makes a lot of sense. Thanks for putting in your valuable
time for thefeedback.
@Ken The Everywhere edition looks good. Will try to get that installed
and check and if works that will make my job a lot easier.

Thanks and Regards
Senthil
Spam Catcher wrote:

"Senthil" <se************@gmail.comwrote in news:1164977472.565260.149500
@l12g2000cwl.googlegroups.com:

But as far as I have worked with SQL, first we need
to install SQL server and restore the database backup in the system. So
the user has to install the SQL compact and then has to do a backup of
the database! Can I do this also in the installer??

You can run a SQL script to build the tables, or ship the database with the
installer.