locked
What would you do. RRS feed

  • Question

  • Hi,

    I have a tenant tracking application that uses an Access 2007 database. The requirement was to be able to share the data between a desktop and a laptop. The stickler here is its in a small town and the manager does not have internet at home. She wanted to be able to work on the app while she was home.

    I accomplish this by making a thumb drive a requirement. What I do is everytime the app is closed a backup of the mdb file is written to the thumbdrive. When she gets to the office she hands the thumb drive to the secretary, she puts it in her desktop and does a restore when the app is started. She now has the current data. When she shuts down the mdb is again backed up to the thumb drive. She hands it to the owner and the process is repeated.

    It seems to work ok.

    Now for my question.

    I have been getting reports of data inconsistancy, tenants missing, payments missing.

    I have learned alot this past year and want to start from scratch and clean up the database design.

    Since I am starting over, what database should I use given the requirements above? Stick with Access 2007? Switch to SQL? Do I have to install SQL Server express on each pc? How do I keep them in sync?

    Any advice would be appreciated.

    Thanks

    MW

    • Changed type Franklin ChenMicrosoft employee Wednesday, April 30, 2014 5:57 AM Discussion
    • Changed type Reed KimbleMVP Wednesday, April 30, 2014 4:04 PM asks a question - should only change to discussion if no clear answer(s) emerge
    Tuesday, April 29, 2014 11:20 PM

Answers

  • After some detective work and hours watching the users via TeamViewer I think i figured out the problem with the data inconsistancy.

    Each time the app is closed, they option is there to backup the db to the thumbdrive. I default the name to the databasename and the current date.

    Well if they opened the app 5 times during the day and closed it, they were appending a number to the end of the filename.

    However when they restore it, they were not picking the correct file..dah

    I have them always using the same file name for that day, choose to overwrite and the problem seems to have gone away. Each day will have its own filename, but that seems to be ok as well.

    Thanks

    MW

    • Marked as answer by MW2011 Friday, May 2, 2014 11:25 AM
    Friday, May 2, 2014 11:25 AM

All replies

  • If you plan to have a shared database where multiple users are gong to read/write data at the same time then don't use Access.  It is not meant for a shared environment and could account for why you are loosing records.

    I like SQL server (or express) a lot.  Yo ucan get express for free and should work in most applications as long as you aren't stroing huge amounts of data.  It is a good back end database.  You can still use either Access or a VS application for the front end of the database as long as they store the data in the SQL Server.

    You only need to store SQL Server on one PC.   Both Access and VS application can remotely connect to the server over the internet.  I would also install SQL Server Management Studio (SSMS) so you can manually configure the database and perform queries without another application.

    If yo uare temporarily storing data in Acces or VS application you can simply connect to the server and perform an update.  The SQL server will automatically do the updating.  I would recommend in the database using a primnary key and include a time/date field for synchronizing records. 


    jdweng

    • Proposed as answer by Renee Culver Wednesday, April 30, 2014 4:09 PM
    Wednesday, April 30, 2014 9:40 AM

  • You only need to store SQL Server on one PC.   Both Access and VS application can remotely connect to the server over the internet.  I would also install SQL Server Management Studio (SSMS) so you can manually configure the database and perform queries without another application.

    If yo uare temporarily storing data in Acces or VS application you can simply connect to the server and perform an update.  The SQL server will automatically do the updating.  I would recommend in the database using a primnary key and include a time/date field for synchronizing records. 


    jdweng

    Really, 

    Without Internet? I thougt that was impossible, in my idea what the OP asks is replication. An often misunderstood almost impossible job to create. It is on SQL server but it has a price.

    http://en.wikipedia.org/wiki/Replication_(computing)

    Be aware this is not the same as one time importing data in a new database

    The alternative from this is synchronizing.

    Both you find on this page

    http://msdn.microsoft.com/en-us/library/ms151198.aspx


    Success
    Cor

    Wednesday, April 30, 2014 10:16 AM
  • Hello,

    If there is inconistancy in data that would most likely be a user issue i.e. user enters data on Monday in the office, does the thumb drive routine, works from home Monday night. Tuesday does not copy database from thumb drive to work computer, adds/edits data on Tuesday, does not copy data to thumb drive, Wednesday copies thumb drive to work computer, changes from Tuesday are lost. This is but one possibility.

    We have one app that has a in-house and out-of-the-office app where the logic is fairly complex with a great deal of assertion to ensure no data problems other than say power interruptions. I would not suggest going down this path but instead a) get a web access like a hot-spot device that is only used for this app which would make it cheap to do b) get Internet service unless it's not possible.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Wednesday, April 30, 2014 11:18 AM
  • It sounds to me like the backup, restore or replication process is not functioning properly. I don't have enough details regarding this process to assess but that would account for the missing or inconsistent data.

    Access, SQL Compact or SQLite should be fine for this implementation because you require database portability. I don't think SQL Server (or Express) buys you anything really since you're not operating in a multi-user networked environment.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, April 30, 2014 1:06 PM
  • ... a tenant tracking application... share the data between a desktop and a laptop... small town... [no] internet at home...

    Since I am starting over, what database should I use given the requirements above? Stick with Access 2007? Switch to SQL? Do I have to install SQL Server express on each pc? How do I keep them in sync?

    ...

    In this situation, I would first determine if a database engine was even necessary at all.  Rental-management software generally does not require a lot of data per-tenant. There may be a table of "Properties" with some basic information such as physical location and regional codes. Each "Property" record might provide the parent id for a table of "Residences" with information about rooms, amenities, furnishings, minimum deposit, occupancy rules, etc. There would be a "Tenants" table where each renter's personal details are stored.  Then you might have a "Rentals" table which links Tenants to Residences with information about the terms of occupancy.  A "Payments" table would hold records for each rent payment made on a Rental.  There might also be a table or two for maintenance and upkeep data, or other relevant actionable data.

    The Properties and Residences tables are liable to be fairly static once populated (how often are new rental properties acquired, sold, or otherwise changed?).  The Tenants, and subsequently Rentals, tables will change with a frequency proportional to the turnover in tenants; how many tenant changes does the business experience on average per year?  The payments table will change monthly with a record load generally proportional to the number of Rentals, and any other actionable data (such as maintenance) can be expected to grow a some regular rate which should be able to be estimated based on history.

    So in a small town, with spotty Internet access, for a rental business operated by a tiny number of people it is probably safe to assume that the number of static records (properties and residences) would be unlikely to reach more than a few hundred, that semi-static records like Tenants and Rentals would be unlikely to exceed more than a few thousand, and that payment and maintenance records would also likely be in the thousands-per-year at most.  The dynamic records like payments and maintenance can also likely be rolled up and archived at the beginning of each new year.

    What all of this tells us is that is unlikely that we will ever need to deal with tens or hundreds of thousands of records at one time, and that each individual record will likely be of a reasonable size.

    Add this to the fact that we really have a single-user application (doesn't matter if that user isn't always the same person) which needs to run on multiple computers without the aid of a network connection, and I think we have a strong argument for just using a DataSet instance and persisting it through serialization rather than through a database engine.

    The one potential downside to this is that the user must save their work more frequently because changes are not persisted in real-time; so if the power went out or the computer crashed, the user would loose any unsaved work.  However, this generally isn't much of a drawback for a single-user of a small application; user's are generally used to needed to save their work.

    The upside is ease of implementation and data consistency.  With a database engine, there is more than just the schema and data being stored; there are also transaction logs containing a lot of information about internal database activity.  While this information can be vital in large and/or multiuser scenarios, in cases like this the information is generally superfluous and could even become problematic.  If you only serialize the contents of a dataset, then you are only storing the schema and data, and information about changes to the data over time are lost (the DataSet still holds information about the most recent changes internally until AcceptChanges is called).  However, you generally don't care about that with an application of this size and scope, and any usage tracking information that you might need could be implemented in your code using an additional transaction table in the dataset (allowing you to minimize the data stored to only the most critical information).

    So if it were me, and all of the above assumptions were generally accurate, I would probably use a little class like the following to just serialize a strongly-typed dataset instance to/from the thumb-drive.

    Public Class DataPersistance
        Public Shared Sub Save(Of T As DataSet)(filePath As String, target As T)
            Using stream As System.IO.FileStream = System.IO.File.Open(filePath, IO.FileMode.OpenOrCreate, IO.FileAccess.Write)
                Using compressed As New System.IO.Compression.GZipStream(stream, IO.Compression.CompressionLevel.Optimal)
                    Dim formatter As New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
                    formatter.Serialize(compressed, target)
                End Using
            End Using
        End Sub
    
        Public Shared Function Load(Of T As DataSet)(filePath As String) As T
            Using stream As System.IO.FileStream = System.IO.File.Open(filePath, IO.FileMode.Open, IO.FileAccess.Read)
                Using compressed As New System.IO.Compression.GZipStream(stream, IO.Compression.CompressionMode.Decompress)
                    Dim formatter As New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
                    Return CType(formatter.Deserialize(compressed), T)
                End Using
            End Using
        End Function
    End Class


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Proposed as answer by Renee Culver Wednesday, April 30, 2014 4:11 PM
    Wednesday, April 30, 2014 4:04 PM
  • Reed,

    I was about to type up something about using Object Binding rather than a database - sort of like your idea - then I saw that you posted so I read it first.

    He has two different computers, so even though it might be the same program on each of them, binary serialization won't be "sharable" (is that a word?) unless it's serialized specifically without keeping the identity - which isn't wise but it might work.

    What about serializing to SOAP instead? It's a hassle, but it can be done.

    For what it's worth. :)


    Please call me Frank :)


    Wednesday, April 30, 2014 4:12 PM
  • Reed,

    I was about to type up something about using Object Binding rather than a database - sort of like your idea - then I saw that you posted so I read it first.

    He has two different computers, so even though it might be the same program on each of them, binary serialization won't be "sharable" (is that a word?) unless it's serialized specifically without keeping the identity - which isn't wise but it might work.

    What about serializing to SOAP instead? It's a hassle, but it can be done.

    For what it's worth. :)


    Please call me Frank :)


    Actually that won't be an issue because both computers are running the same application.  If each computer had a separate application assembly then yes, this would have to be taken into account (likely using a custom binder as Luc has shown in the MemoryMappedFile thread).


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Wednesday, April 30, 2014 4:48 PM
  • Actually that won't be an issue because both computers are running the same application.  If each computer had a separate application assembly then yes, this would have to be taken into account (likely using a custom binder as Luc has shown in the MemoryMappedFile thread).

    That depends on what he has in mind. I assumed the program itself would be installed on two computers and only the "data" to be shared.

    I think we're guessing until we hear back from him. ;-)


    Please call me Frank :)

    Wednesday, April 30, 2014 4:52 PM
  • Thank you for all the replies.

    As I mentioned, the boss does not have internet at home.

    I think I am going to stick with the access database concept. What about moving the mdb to the thumbdrive, basically without it the app won't work.

    That way when the manager goes home and works on the db, she will be working with the one actually on the thumb drive. She goes to the office the next morning and hands the thumb drive to the secretary for the day. She also works off the thumb drive. End of day gives it to the boss.

    A what if is, what if the boss dont come in. No DB work today..lol

    I have some logic that is checking the file attributes and which ever is newer wins.

    As far as the data inconsistancies, I can't recreate it, I remoted into their machine launched the app and had them show me what was missing. I then copied the DB to my machine with the app installed (same version) and I can see all the data.

    I am using clickonce deployment to my Dropbox account, its been working perfect. but for the heck of it I think I will un-install the app on their machine and re-install it to see if by chance they are not getting all my updates.

    I am also completely starting over on the project and am taking care to make sure all the PK, FK are correct.

    I am totally an amature .net developer, but they love my app (aside from a couple minor issues) so I will stick with it til I get it right.

    Thanks for all the input

    MW

    Wednesday, April 30, 2014 11:47 PM
  •  I assumed the program itself would be installed on two computers and only the "data" to be shared.

    Please call me Frank :)

    That's what I mean as well. I'm saying it will work fine because the serialized object is defined in the application assembly. So any instance of the application, on any machine, can deserialize a file from any other application instance. Its the same assembly (assuming identical version numbers).

    The problem you described only arises when you have a type defined in, and serialized from, ExecutableAssemblyA that you want to deserialize from ExecutableAssemblyB.  Then you have to take extra steps to avoid having an assembly requirement.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, May 1, 2014 12:03 AM
  • Access used to support replication but it was dropped in the newer format. What I would recommend is creating a backup of the data before copying over a current database version on each computer. At least that should allow you to track changes and possibly discover where data went missing. I really don't think that it's Access itself that is the problem, so it should be fine to continue to use it.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, May 1, 2014 12:12 AM
  • That's what I mean as well. I'm saying it will work fine because the serialized object is defined in the application assembly.

    Yea, you're right - I was thinking of using the serialized file in another program that didn't have the same assembly name.

    So long as the AssemblyName, Version, and Culture are the same, it should work. Mea culpa.

    Thanks :)


    Please call me Frank :)

    Thursday, May 1, 2014 12:25 AM
  • After some detective work and hours watching the users via TeamViewer I think i figured out the problem with the data inconsistancy.

    Each time the app is closed, they option is there to backup the db to the thumbdrive. I default the name to the databasename and the current date.

    Well if they opened the app 5 times during the day and closed it, they were appending a number to the end of the filename.

    However when they restore it, they were not picking the correct file..dah

    I have them always using the same file name for that day, choose to overwrite and the problem seems to have gone away. Each day will have its own filename, but that seems to be ok as well.

    Thanks

    MW

    • Marked as answer by MW2011 Friday, May 2, 2014 11:25 AM
    Friday, May 2, 2014 11:25 AM
  • After some detective work and hours watching the users via TeamViewer I think i figured out the problem with the data inconsistancy.

    Each time the app is closed, they option is there to backup the db to the thumbdrive. I default the name to the databasename and the current date.

    Well if they opened the app 5 times during the day and closed it, they were appending a number to the end of the filename.

    However when they restore it, they were not picking the correct file..dah

    I have them always using the same file name for that day, choose to overwrite and the problem seems to have gone away. Each day will have its own filename, but that seems to be ok as well.

    Thanks

    MW

    Can't the apartment complex or whatever tracks the tenants afford a cheap wireless broadband dongle or hotspot for the manager?


    La vida loca

    Friday, May 2, 2014 12:10 PM