[AccessD] Upgrade Access to SQL Server

The time would be anywhere from hours to weeks or longer.

The "stored locally" is precisely how it is done at the moment.  I am being asked to up-size the 
tables to SQL Server and of course, if I just do the request, we will have all the users mixing 
their data into the same table.  There are existing append / update / delete queries that are 
triggered in various ways, including by the push of buttons.  I can't be more specific simply 
because this is a general question about many different databases.

In any event I will have to analyze what every query does to prevent multi-user issues.

John W. Colby

Reality is what refuses to go away
when you do not believe in it

On 1/31/2013 2:16 PM, David McAfee wrote:
> You can look into # and ## temp tables.
>
> One allows you to keep the temp table for your session, for your user id,
> the other allows the table data to be read from other users.
>
> I've had them get created on a Form open event and drop when the form
> closes.
>
> It all really depends on how much time the user is going to need them, is
> it for a few minutes or are we talking days (or longer?).
>
> I really don't like the idea of a user having his own table stored locally.
>
> your suggestion at the end is probably the best thing to do.
>
> D
>
>
>
> On Thu, Jan 31, 2013 at 11:06 AM, John W Colby <jwcolby at gmail.com> wrote:
>
>> I am being asked to upgrade Access FEs which have quite complex SQL Server
>> BE tables, plus (apparently) some data from those tables pulled down to the
>> FE and stored there over time as the user processes the data in those local
>> FE tables.  They want to move those local tables to SQL Server.
>>
>> My question is, is there an accepted method for providing this kind of
>> table out in SQL Server?  IOW the structure is there, but the data in the
>> table (as seen from the FE ) belongs to that instance of the FE.
>>
>> We place tables local to the FE exactly for this purpose, to make it local
>> to that specific instance of the FE, on that specific user, on that
>> specific machine.
>>
>> It seems that if I am going to do this in SQL Server then I will need to
>> add a "machine ID" kind of FK in the tables as I upsize them to SQL Server,
>> then in the Access Application somehow get filtered datasets.  This sounds
>> ugly.
>>
>> --
>> John W. Colby
>>