Using Links in Ms Access to SQL Server Database
Posted on 2004-04-12
I am using Access database in current version of my program.But Due to problem in data corruption in multi user environment.I want to transfer only those database to SQL Server, where no of users are more.
So I don't want to change database completely.User will select same MDB file which contains Links of Database of SQL Server.
I have provided Links of SQL Database in Access file.I am trying to connect this database through ADO Code.
Dim WithEvents adoPrimaryRS As Recordset
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=MS Access Database;uid=;pwd=;"
Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open "select * from Table1", db, adOpenStatic, adLockOptimistic
But When I am trying to connect MY Access Database which contains links of SQL Server.I think connection to SQL Server must be given programatically while connecting to access database.
I am getting Error [microsoft][ODBC Microsoft access Driver]ODBC--Connection to 'MQIS' Failed
FYI My access database showing me data of SQL Server.and I can Update records by Access forms also.Now I want to do this By My Visual Basic Application at run Time
Please help me how to solve this problem.