Soldato
This is kind of following on from a thread made some time ago (http://forums.overclockers.co.uk/showthread.php?t=18321770) but thought I'd start a new one as it isn't really related to Access anymore.
I'm trying to move my current setup into SQL (learning curve - totally new to SQL!) and am getting stuck at how to get the ODBC data from Sage into SQL Express.
In Access I have "Linked Tables" connected to the ODBC driver which works find (read only) this is exactly what I want in SQL Express too. From doing research it seems I need to add "Linked Server" and this is where I'm not having any luck.
The Sage installation where data files are stored are on another machine (both XP Pro) on the same network - Sage works fine on the machine I'm trying to get the data from, and I can use the User DSN and System DSN in ODBC Data Source Adminsitrator in Access to pull all the tables in, so I'm sure the network isn't the issue here.
Below is a screenshot of the setting in SQL Express
Which returns the following when I try to add it (Authentication failed) :
I can get around that by changing the login to "Be made using the login's current security context" as below. This lets me add the Linked Server without generating any error messages.
Once the server is added, I can't actually see a list of tables or anything - get a similar error "Access Denied"
Funnily enough when I try to test connection it tells me "The test connection to the linked server succeeded"
I'm not sure what else to try here ... any tips for getting me past this?
I guess in theory I could create an ODBC Link to Access then link that to SQL , but would prefer to do it the above method if I could get this to work!
I'm trying to move my current setup into SQL (learning curve - totally new to SQL!) and am getting stuck at how to get the ODBC data from Sage into SQL Express.
In Access I have "Linked Tables" connected to the ODBC driver which works find (read only) this is exactly what I want in SQL Express too. From doing research it seems I need to add "Linked Server" and this is where I'm not having any luck.
The Sage installation where data files are stored are on another machine (both XP Pro) on the same network - Sage works fine on the machine I'm trying to get the data from, and I can use the User DSN and System DSN in ODBC Data Source Adminsitrator in Access to pull all the tables in, so I'm sure the network isn't the issue here.
Below is a screenshot of the setting in SQL Express
Which returns the following when I try to add it (Authentication failed) :
I can get around that by changing the login to "Be made using the login's current security context" as below. This lets me add the Linked Server without generating any error messages.
Once the server is added, I can't actually see a list of tables or anything - get a similar error "Access Denied"
Funnily enough when I try to test connection it tells me "The test connection to the linked server succeeded"
I'm not sure what else to try here ... any tips for getting me past this?
I guess in theory I could create an ODBC Link to Access then link that to SQL , but would prefer to do it the above method if I could get this to work!
Remote connections are set up properly