July 2, 2003 at 6:52 am
Hi, i'm new to sql server. (and this site - so if im posting in the wrong place i'm sorry) I have a fair amount of experience with other database systems, but I need to do something fairly specific and was wondering if anyone could help.
At the moment there is a database in access with around 500,000 records in it. Which is totally unmanageable. SQL server is available on my network so i thought moving the tables into it would be helpful. The problem is that everyone in the office needs to be able to manipulate the db and create their own reports etc. So i though the best way to do this would be to create links into the db from access front ends on their pcs using odbc.
Firstly, - will this work ok - will the queries that were created in access function better on the odbc linked tables? - Is there a better way to do this?
Next, three of the main tables in the system get totally replaced every month or so. The people that need to do the updating are not administrators, so what would be the easiest way to do this?
- Basically what i need is someway for them to place the db file in a shared folder on the network and then run some form of script to delete a selection of tables from the sqlserver db and then import the new ones from the access db. All that can executed from the client machine.
Anyone have any ideas...
David.
July 2, 2003 at 7:40 am
For the first problem.
Creating 'linked' tables should work. The procedures might be more efficient since the SQL server engine is more powerful. However, you do introduce network overhead, so this might reduce the gain.
Another problem might arise if you are using complex, updatable queries in Access. Some threads have been around that report problems with those.
For the second part, you could create a DTS task that checks for the file and if it finds it, does the necessary handling through a bulk insert task.
July 2, 2003 at 7:47 am
Thanks, thats very helpful.
What kind of problems are likely to occur using the linked table system - what are the alternatives?
For the second part to my question - would an access user be able to link to the DTS task an activate it?
July 3, 2003 at 8:58 am
You can run a DTS task from a client application written in VB. Check out the SQL Server Books Online (BOL) or check out this link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtspapps_2e5f.asp
Joe Johnson
Joe Johnson
NETDIO,LLC.
July 8, 2003 at 3:18 am
Cheers, thats very helpful.
I'll look into it.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply