Using Links in Ms Access to SQL Server Database

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

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

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.

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

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

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

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

"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...

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

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

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

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/questions/20949054/points-for-alanwarren.html

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

and think another thing that may be relevant is that my ODBC links are set up to Use Trusted Connection

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

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 ......

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