Solved

Using Links in Ms Access to SQL Server Database

Posted on 2004-04-12
24
1,067 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 11

Author Comment

by:sparab
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
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
Comment Utility
"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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Expert Comment

by:Alan Warren
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Good one sparab!

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

Alan
0
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
Comment Utility
PAQed, with points refunded (125)

Computer101
E-E Admin
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

763 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now