Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB.NET 1.0 > BOOK: Beginning VB.NET Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning VB.NET Databases
This is the forum to discuss the Wrox book Beginning VB.NET Databases by Thearon Willis; ISBN: 9780764568008
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning VB.NET Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old August 6th, 2007, 06:44 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default How can I populate large table with unique Guids?

Hi guys - can any of you help me please?

I've got an MS Access database table that has a primary key which is an autonumber field. This table has 5 tables attached to it, but because the (vb.net) application has now become multiuser, I now have a concurrency issues. So I want to change all the autonumbers to Guids and need a routine to do this please.

I was thinking about creating a table of guids (the size of the table's) and then doing some sort of loop and UPDATING each autonumber field with the created GUID from the table of guids but I'm not sure if this is the best approach. I have got 'cascade updates' checked within MS Access, so I am hoping the guids will replicate down into the attached tables too?

Any help will be greatfully recieved,

Thanks,

Reply With Quote
  #2 (permalink)  
Old August 6th, 2007, 06:13 PM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 63 Times in 63 Posts
Send a message via AIM to dparsons
Default

O.o Simply changing your unique identifier is not going to prevent a concurrency issue. A better suggestion might be something like this (this is just one method but you can apply the methodology anyway you like): When a user access a record, (assuming you store a last modified datetime field associated with each record) store the last modified datetime in a value along with the other values you retrieve from the database and when the user goes to update the record again, compare the last modified time in the database to the last modified value that the user retrieved from the database.

If the 2 values match you can, with a fair degree of certainty, assume that the record was not changed from the time the user pulled the record from the database to the time the user went to update that data and should allow their update into the system. If the values do not match, another user has updated the record between the time you grabbed the data and the time you are going to update the data. You now have to fall back on your business rules as to what to do. Essentially it comes down to this:

 -you can totally discard the current users changes and allow the data in the table to stand
 -allow the current users data to over write that which is in the table (Last in wins)
 -Attempt to merge the data

Alternatively you could add a Bit column to your table that would act as a "Lock" so that when User A requests the data you set the bit value to 1 and it will remain one until the user updates the data and flips the bit value back to 0. During the time that user A is working with the data you should have a process in place that always checks when a user requests data from the table that the value of bit column for a requested row is set to 0, else the request fails.

I understand that you are working with 5 tables but you haven't explained if a user access all 5 tables of data per single request of if they access them one at a time for various operations.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========
Reply With Quote
  #3 (permalink)  
Old August 7th, 2007, 04:40 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks ever so much for the reply. That's really helpful and has got me thinking....but just to add to the problem, here's the situation....

I have an Access database that has 8500 records in the main table (we'll call it table A for simplicity). Table A currently has a primary key which is an autonumber (1 to 8500). There are 5 other subsidary tables B, C, D, E and F all pocessing the foreign autonumber key but of course its a long type in each of these tables.

The Access database is sent out to 4 users who add, modify and delete records in table A to F. After a month I then gather back each copy of the database and attempt to amalgamate each one back into a single copy, then send it out again and repeat the cycle. Well I'm sure you can appreciate that this will be pretty much impossible to do due to 2 users doing an 'add' and creating 2 records with the same primary key. Modifications and deletions would also be to different records so I need to find a better way of storing uniqueness against each new record. Could you give me some ideas if you understand so far?

Thanks,

Reply With Quote
  #4 (permalink)  
Old August 7th, 2007, 07:15 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 63 Times in 63 Posts
Send a message via AIM to dparsons
Default

Do you mean to say you are trying to do a distributed database without a RDBMS such as SQL Server?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========
Reply With Quote
  #5 (permalink)  
Old August 8th, 2007, 03:53 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, I have been using an Access database.††It resides in a directory on a webserver. The users download the latest copy, add, modify and delete records within it. Then one by one, they will run an update programme that compares their database with the webserver's and updates the changes. After the update, they transfer the new database to the weberver and the next user downloads it, updates it with their changes, transfers it etc.
Reply With Quote
  #6 (permalink)  
Old August 8th, 2007, 11:50 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 63 Times in 63 Posts
Send a message via AIM to dparsons
Default

Unfortunately, I am not really sure how to overcome this because there is no real intelligent way to determine which copy of X record is the most accurate one. For example:

User A and B download the data
User A updates row 5 with some data around 9am
User B updates row 5 with some data around 1pm
User A posts their changes back to the webserver and their copy of row 5 overwrites row 5 on the server
User B posts there changes back to the webserver and their copy of row 5 overwrites row 5 on the server that User A previously updated.

Which data do you keep? Which is more accurate? It is possible to write a, for lack of a better term, "merge" application that would pull row 5 from the database and compare it against row 5 that is being uploaded and then you would have to rely on business process's to make the determination of what data should be merged onto the server.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========
Reply With Quote
  #7 (permalink)  
Old August 8th, 2007, 11:58 AM
Thearon's Avatar
Wrox Author
 
Join Date: Dec 2003
Location: Fuquay Varina, NC, USA.
Posts: 396
Thanks: 0
Thanked 8 Times in 8 Posts
Default

It sounds like this application needs to take advantage of SQL Server replication. I worked on a simliar application where the clients were disconnected most of the time and when they did come online - they used SQL Server replication to merge their changes with the master database.

Thearon
Reply With Quote
  #8 (permalink)  
Old August 22nd, 2007, 06:52 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thearon, I've been looking into the possibility of 'replication' and I think MS Access natively supports this.

If there's a chance to supply the 2 tables and let Access do the hard work sorting out which data has changed, then I'll use this instead - much less work.

I'll let you know how I get on....

Thanks both of you - you've been a great help.

Reply With Quote
  #9 (permalink)  
Old August 22nd, 2007, 07:12 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 63 Times in 63 Posts
Send a message via AIM to dparsons
Default

Here is the MS Support document on MS Access Replication: support.microsoft.com/kb/182886

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Professional Search Engine Optimization with ASP.NET

Professional IIS 7 and ASP.NET Integrated Programming

================================================== =========
Reply With Quote
  #10 (permalink)  
Old August 23rd, 2007, 04:18 AM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks very much dparsons.

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract 8 Tables into 1 Large Table hewstone999 Access VBA 1 March 4th, 2008 08:19 AM
Populate a List Box with Table Names & Table date hewstone999 Access VBA 1 February 27th, 2008 09:10 AM
Inserting unique records from a table to another elygp SQL Server 2000 0 September 10th, 2007 01:58 AM
Access Database and Large Excel Table xpromac MySQL 1 October 24th, 2006 05:38 PM
Temp Tables vs. Large Table DaveGerard SQL Server ASP 0 August 4th, 2005 10:08 AM



All times are GMT -4. The time now is 05:06 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.