May 18, 2005 at 4:06 am
Folks,
I have to populate a sql server database which has to be continually updated with data that is collected in an Access Database on a remote server. My first question (probably the easy one) is:- What would be the best way to implement this, given that it has to be updated frequently (5 minute intervals) and that the remote server and remote database cannot be tampered/amended with in any way. Unfortunately the data in the Access DB gets overwritten every half hour and some of the tables don’t have primary keys. I have the administrator password for the logon and the Access DB uses the standard ‘Admin’ user without password?
My next question was a prerequisite to implementing the first one… connecting to that Access database. I was trying to use a linked server to establish the connection between the two databases (without mapping a drive). I have created my linked server ‘MYLinkedServer’ with the following fields
· Product Name :- Access 2002
· DataSource:- \\RemoteServerName\c$\RemoteDatabase.mdb
I have tried all sorts of permutations in the security options and either get the Msg 7399 with the “Not a valid account name or password” or “Could not find file \\RemoteServerName\c$\RemoteDatabase.mdb”
It would appear that I can either connect to the remote server and not connect to Access or I’m just unable to connect period.
Please help. Thanking you in advance,
Axel.
May 18, 2005 at 7:34 am
What type of account does your SQL Server run, domain user or local system account?
May 18, 2005 at 7:59 am
Unfortunately local system account.
May 18, 2005 at 8:06 am
In order to see your MDB file in remote server from SQL Server, you have to use domain user account to run SQL Server service and that domain user account must able to access the file.
May 18, 2005 at 8:28 am
Oh dear - and I thought I was just missing something. Looks like I'll have to copy the whole .mdb across on a regular basis, then do the trawling of data into SQLServer. Not very neat.
Is there an easy way of comparing all the rows in the original table against the new table, then updating the new table with the 'new' rows? While the Access DB cannot be changed, I'm free to create the SQL DB as required. I realise I don't want to be comparing every row in Access with every row in the SQL DB, but as the data in Access is not very well structured a horrible time consuming SQL Query might be the solution.
Thanks for your prompt response.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply