Solved

Using Links in Ms Access to SQL Server Database

Posted on 2004-04-12
24
1,071 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.
0
Comment
Question by:sparab
  • 7
  • 5
  • 5
  • +2
24 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10804231
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

0
 
LVL 11

Author Comment

by:sparab
ID: 10804483
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
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10804584
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
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 11

Author Comment

by:sparab
ID: 10804697
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.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10804706
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
0
 
LVL 11

Author Comment

by:sparab
ID: 10804921
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10804972
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
0
 
LVL 11

Author Comment

by:sparab
ID: 10805339
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.
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10805445
"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
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10805700
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
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 10805995
"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...
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10806287
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



0
 
LVL 36

Expert Comment

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


0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10806450
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 :)
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10806456
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
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 10806554
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..

http://www.experts-exchange.com/Databases/MS_Access/Q_20949054.html
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 10806570

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

0
 
LVL 36

Expert Comment

by:SidFishes
ID: 10806580
and think another thing that may be relevant is that my ODBC links are set up to Use Trusted Connection
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10806623
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


0
 
LVL 11

Author Comment

by:sparab
ID: 10810729
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 ......
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 10810824
Good one sparab!

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

Alan
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10812285
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
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 11190647
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question