Local SQL database on client machine?

Question

Local SQL database on client machine?

christy2931

Old Hand

Points: 395
More actions
January 6, 2004 at 7:37 am

#83897

Hi there,
I'm in the process of upgrading my Access databases to SQL Server, and I have a question.
I've already rewritten my code to use ADO instead of DAO, and I've just migrated my database from Access to SQL Server.
The way my application works is as follows - it's an internal data-entry application so all users are on the network. The VB front end is installed on user machines, and there is a local Access database on each machine. Throughout the day, each user adds new records to his local Access database, and then at the end of the day he upload his new records to the central database located on the server.
My question is this - can each user have a local SQL database on his local machine, add records to it all day, then click the application's UPLOAD button to move all those new records to a central SQL Server database on the server? Obviously, all users will have SQL client tools installefd on their machine. Please tell me it's possible to have SQL databases on local machines that are not servers. 🙂
Thanks!
CHristy

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

Hi Christy -

You could install the MSDE version of SQL Server on each client machine and then have a local SQL Database that could communicate with the server copy.

Do you have a business reason for not letting the users insert directly to the server table? ie slow internet connections, remote offices?

MSDE is the desktop version of SQL Server and can be installed on various Desktop operating systems.

Michelle

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Would that make sense?

I can understand when someone uses this approach (local dbs' that get uploaded during off-peak hours) when you were using Access, but with SQL Server I would directly update on the server, if there were no other reasons like those Michelle mentioned.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

christy2931 Old Hand Points: 395 More actions · Answer 3

Hi Michelle,

Thanks for your reply! I'm not sure if I should allow each user to connect directly to the server database or not. Here's my reasoning...

In designing the application, it was supposed to be multi-user, and we needed the separate databases for a variety of reasons

Right now, our application is NOT multi-user - only one person can enter data at a time (in the future, we hope it make it multi-user again, but we can deal with that when the time comes, as there are a LOT of changes that will need to be made then as well). Before the next person begins to use the application, the current user must upload their new records, and the new user must refresh their local database with the last person's records. I guess there's really no reason why each user can't connect directly to the server database - that would probably be more efficient, as each user wouldn't need to continually upload and refresh their local databases.

Hmmmm, I shall have to give this some more thought, to ensure that it won't mess anything up. My goodness, the stress! 🙂

THanks Michelle!

Christy

rcnelsonmba SSC Eights! Points: 813 More actions · Answer 4

I would definitely update SQL Server directly. T-SQL can be written in a way as to have only 1 user update a specific set of data at any given time. Obviously, there may be a lot of coding to do if the application is in anyway sophisticated. My opinion (for whatever it is worth) is that if you don't have the time to do it right the first time, when will you ever get the chance to do it right the 2nd time?

Good luck.

Ron

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

I would definitely redesign your application.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 6

Christy -

I have lived through the nightmare of having small databases local and then needing to combine them. If at all possible, make changes so your users can insert to the server.

Michelle

christy2931 Old Hand Points: 395 More actions · Answer 7

Thanks again, Michelle! From what I can figure, it shouldn't affect much to have users insert directly into the server database - a couple of things, but I can rewrite the code to handle it fairly quickly. SO I've decided to try it the way you and the other people suggested.

I've changed my connection strings to connect to the server SQL database, and the connections seem to be fine, i.e. I don't get any errors when it opens the database or the recordsets. However, it's REALLY slow, and sometimes times out when trying to do certain things.

For instance, I can open the connection to the server database, open a recordset from one of the tables, naviagate thru the recordset, but it takes about a full minute to movelast or movefirst! Then when I go to the add new record form, i can open the connection okay there, and open two recordsets from two different tables, but then when I try to move to the last record of one of the recordsets, it chugs away for a minute or so, then I get a timeout error every time. I can't figure out why!

Any ideas?

Thanks! Christy

rcnelsonmba SSC Eights! Points: 813 More actions · Answer 8

Have you looked at the table(s) you are navigating thru and look at how they are indexed? Chance are with upgrading from Access to SQL Server that there aren't any.

christy2931 Old Hand Points: 395 More actions · Answer 9

Hmmm, no I didn't check that. I guess I should make sure the right fields are indexed - I'm off to figure out how to do that haha!

Thanks!

Christy