We help IT Professionals succeed at work.

Using Links in Ms Access to SQL Server Database

sparab
sparab asked
on
1,111 Views
Last Modified: 2008-02-01
Hi

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.
Comment
Watch Question

Alan WarrenApplications Developer

Commented:
Hi sparab,


Public Sub doConnect()
  Dim cn as ADODB.Connection
  Dim sConnect As String
  sConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=YourDataBase;DATA SOURCE=YourSQLServer"
  Set cn = New ADODB.Connection
  cn.CursorLocation = adUseClient
  cn.Open sConnect
  If cn.State <> 0 then: MsgBox "Connection OK"

End Sub


Alan

Author

Commented:
Dear Alan,

Thanks for Information.I know how to connect to SQL server, which is not a big thing.But I Like to know how I could connect SQL database  through access Database which is MDB file and having Link of SQL Server.

I don't want to change anything in my runtime EXE.I want My access file to connect automatically to SQL Server,When My runtime VB program connects to access database.This avoid my lots of work to convert database platform of existing access to  SQL.

Regards
Sparab
If you simply open Access, and then on the Tables Tab, elect New/LINK Table---there you can create links to the SQL Server tables, and you Access application will function EXACTLY as if the LINKED tables were REAL tables in Access.

But your question raises another issue.  You say that you are moving the data to SQL Server due to Data Corruption issues.  So I ask you this, is your CURRENT Access application using a SPLIT database.  That is do you have the FORMS, QUERIES, REPORTS in a 'FRONT-END' MDB, with the tables LINKED to a Back-end MDB?  I suspect that you have no idea what I am talking about, and that your current application has the forms AND tables ALL in the same single MDB.  That is almost certainly the reason for your data corruption issues, and NOT the number of users.

If you want to SPLIT the databse, use the Database Splitter Wizard, which you can find under the Tools/DAtabase Utilites menu choice.  If you SPLIT your current MDB into a Front-end and Back-end , your datacorruption issues may disappear, without the need to migrate the data to SQL Server.

AW

Author

Commented:
Dear Arthur,

Yes I have tried the concept of Split Database and allowing 5 users to access database through this.But My Entire team is around 50.So I have to maintain database three times a day.


FYI----My MDB-Access file contains only Tables.and some queires which i am using to check database usually.My entire front end code is in visual basic.
Alan WarrenApplications Developer

Commented:
Hi Sparab,

Just tried to do what I think you are attempting to do.

That is:
Use a vb app to connect to a Jet mdb, that in turn has linked tables to a sql server catalog,
and open a recordset on one of the linked SQL tables in the Jet mdb from the vb app.

I created a new MDB called LinkToSQLNorthwind.mdb and linked to sql server Northwind catalog Table!dbo.Customers, then closed and saved the MDB

Then I created a VB Project and placed this code in the form_Load event.
The error I got was this:
' Error number 3709 was generated by ADODB.Recordset
' The connection cannot be used to perform this operation.
' It is either closed or invalid in this context.

=======Begin Form Code=============
Private Sub Form_Load()
  On Error GoTo ReportError
 
  Dim sDataFile       ' full path to mdb file in LinkToSQLNorthwind.mdb
  Dim sPassword       ' file-password of mdb file
  Dim cn As ADODB.Connection
  Set cn = New ADODB.Connection
  Dim rs As ADODB.Recordset
  Set rs = New ADODB.Recordset
  Dim sql As String
 
  ' mdb file, password and connect string
  sDataFile = App.Path & "\LinkToSQLNorthwind.mdb"
  sPassword = ""
  sDataConnect = adoConnectJet40(sDataFile, sPassword)
 
  cn.CursorLocation = adUseClient
  cn.Open sDataConnect
  If cn.State <> 0 Then
    MsgBox "Connection OK"                                            '<--  this returns OK
    sql = "Select dbo_Customers.* from dbo_Customers"
    rs.Open sql                                                               '<--Errors here
    If rs.State <> 0 Then
      MsgBox rs.Fields(0).Value
    End If
  Else
    MsgBox "Rs failed to open"
  End If
 


ExitProcedure:
  On Error Resume Next
  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing
  Exit Sub

ReportError:
  Dim msg As String
  msg = "Error in Form_Load()" _
    & vbCr & "Error number " & CStr(Err.Number) _
    & " was generated by " & Err.Source _
    & vbCr & Err.Description
  MsgBox msg, vbExclamation + vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext
  Resume ExitProcedure
 

End Sub

Public Function adoConnectJet40(psDataPath, psFilePassword)
 
  ' returns Jet 4.0 ADO connect string:
  '   "Provider=Microsoft.Jet.OLEDB.4.0
  '     ;Data Source=C:\xxx\Some.mdb
  '     ;Jet OLEDB:Database Password=xxx"
 
  Dim sProvider, sDataSource, sDBPassword

  sProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
  sDataSource = ";Data Source=" & psDataPath
  If psFilePassword = "" Then
    sDBPassword = ""
  Else
    sDBPassword = ";Jet OLEDB:Database Password=" & psFilePassword
  End If

  adoConnectJet40 = sProvider & sDataSource & sDBPassword

End Function

======== End Form Code==============

So it looks to me as if it cannot be done, but someone else may know how.

Regards

Alan

Author

Commented:
Dear Allan

thanks for the efforts.

Yes It can be done....Only thing I have to do When User are trying to connect database MDB file.I want to logon to SQL Server Anyhow through Access.
sparab, if you have data corruption errors in the MDB with only 5 users, then you have some other infrastrcutuire issues that are NOT related to the Database.  I have had MANY applicactions, using Access as the Backend, with VB as the Front-end with as many as 80 users, and NEVER encountered any corruption issues, much less having to rep[air the database 3 times a day.  I would examine the application istelf (your VB code), and also the network and other infrastructure items.  If the problem is infrastrucute related, moving to SQL Server for an indirect back-end (you rpogram connections to Access MDB, which holds LINKS to the SQL Server true back-end) will NOT relsove the problems, and may in fact cuase your performance to degrade, as you are now creating a double-link system.

If you MUST use SQL Server for tha back-end, what is the problem with connecting you VB Front-end DIREECTLY to eh SQL Server backend.  I do not understand why you want to maintain the MDB as in intermediate connection.

AW

Author

Commented:
Dear Arthur

Thanks for all your suggestion.Cause for which I don't want to change entirely to SQL database are..
My program is already completed and I don't want to cheange entire system again.
My program is working on different location where SQL Support is not avaiable.And alos Teams are very less.
"My program is working on different location where SQL Support is not avaiable", then you can't use SQL Server as the back-end to hold your data.

Do you have the Database Connection HARDCODED throughout your program?  Is that the reason for not linking direectly to SQL Server?

what do you mean by "also Teams are very less"?

AW
Alan WarrenApplications Developer

Commented:
Hi Arthur,

re: intermediate connection
I wasn't aware that this could be done, I cant seem to be able to make it happen here. :(

Alan
CERTIFIED EXPERT

Commented:
"But I Like to know how I could connect SQL database  through access Database which is MDB file and having Link of SQL Server."

the simplest way to do this is to:

set up an ODBC system dsn to your SQL server
use the link tables and choose ODBC as type of file
point it to your dsn and you're done.

important to note that you'd have to set up a DSN on every machine that will use the db...
Alan WarrenApplications Developer

Commented:
Hi Sid,

think you got the wrong end of the stick mate.

We have an MDB called LinkedSQL.MDB which is ODBC linked to sql server, no problem, open Linked tables, edit data, the works.
Close the mdb.

Open a Visual Basic 6 project, set up an ADODB.Connection to the Jet MDB(LinkedSQL.MDB ) that contains linked sql tables.

Now return a recordset from one of the LINKED SQL server tables,
any kind of recordset, readonly will do.


That is my understanding of this question.
I cant do it, can you?
How?

Alan



CERTIFIED EXPERT

Commented:
wrong end of the stick?....i don't even have the right stick...totally missed the point of the q...sigh...


Alan WarrenApplications Developer

Commented:
dats cool mate happens to me all the time. Actually it may be me that has the wrong end of the stick. Oh well...

Alan :)
As far as I am aware, it is possible to Open a Connection from VB to a Access Database, where the 'Tables' in the Access DB are themselves Links to other tables in other DBs.  It means establishing the LINKED tables in Access first, and not in code.  Now there may be a problem if the LINKED tables (in SQL Server) require a Username and Password, but I have done it on non-secure (using Integrated Security) SQL Server Databases, so I am reasonably sure that it can be done.

what you need to do, when creating the LINK in Access, is to check the "save password" checkbox,when linking to the Selected SQL Server table.  I just did it, and works like a charm!!!  The checkbox is below the Buttons, on the Right side of the Link Tables dialog form, to select the Table(s) to be linked into Access, from the remote database.

AW
CERTIFIED EXPERT

Commented:
now that i understand the q...(i think)

i can return records through the linked tables in the mdb to the sql server..i didn't do this prgrammatically...just using the object interface

first the linked tables in the mdb are odbc...

created a datalink to my mdb
drop a data object on the form, assign a table in my datalink mdb as a recordsource
drop a text  box on the form assign it to CustomerID

i can scroll thru the customerid no problem...


btw...Alan...there's some pts here for ya..

https://www.experts-exchange.com/Databases/MS_Access/Q_20949054.html
CERTIFIED EXPERT

Commented:

this is in VB 6.0 btw...

created a datalink to my mdb
drop a data object on the form, assign a table in my datalink mdb as a recordsource
drop a text  box on the form assign it to CustomerID

CERTIFIED EXPERT

Commented:
and think another thing that may be relevant is that my ODBC links are set up to Use Trusted Connection
Alan WarrenApplications Developer

Commented:
Hi Arthur,

You champion.

I figured out what I was doing wrong.

rs.Open sql
-------------^----- no connection object in the open arguments

rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly   '<-- dis works

Cheers mate... I didnt know you could do that.
It really does pay to air ones ignorance in public.. LOL

Alan


Author

Commented:
Hurray

I got the Solution for it.....

What you have to do is Create Login In Access ->Tools->Security->Crate User Login.....Assign Permissions To Users.

Then Link SQL Tables....Save Passowrd.....

And In the Code Only thing You have to do is Pass the Login Name ion the Connection Statement....

db.Open "PROVIDER=MSDASQL;dsn=MS Access Database;uid=sa;pwd=;"

It works like a Charm....And There is no need to change my entire code to change for SQL Database ......
Alan WarrenApplications Developer

Commented:
Good one sparab!

glad you got it sorted out.
I learnt something too :)

Alan
just proves the old adage---Give a man a fish and you feed him for a day.  Teach a man to fish for himself, and you feed him for the rest of his life.

Good on ya, sparab.

AW
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.