Hope I'm posting my question to the right place?
I'm new to SQL Server. I have a project to migrate an MS-Access small database to SQL Server. So far so good, except I am running into the problem of keeping data synchronized across connected users such that, if UserA creates a new record, UserB won't know until he "refreshes" his copy of the database. I'm using VisualBasic with TableAdapters. Right now, because of testing limitations, I created ONE single SQL Server Login (UserID= XXX and UserPassword = YYY). What happens is that my application is installed on a number of stations on the network and everyone is connecting with the same credentials. This "model" works fine until each users starts to add, modify, update and delete.
I suspect I ought to switch Authentication models and use Windows Authentication to access the server, as a first step? But I have no idea how that will fix the 'synchronization' issue? I suspect some kind of communication "mechanism" has to go on between "connected" users? But, to the best of my understanding, using TableAdapters and DataSets, I'm in a "disconnected" mode. I don't know the limit of this model and whether to give each users separate account will help at all?
I was thinking about adding a Timer to my application and issue a "refresh" every 10 minutes or so, like this :
Me.ProductTableAdapter.Fill(Me.SQLDataSet.Products)
Any help is appreciated.
