Solved

Access database with linked tables to sql, How do I setup a ADO connection

Posted on 2014-11-18
17
374 Views
Last Modified: 2014-11-20
I have a access database, with several linked tables.  By Sql Server is GCERP, my Database is called GC_Emp_data, is there away on page load that I can setup an automatic connection to there server in VB?..  with an ADO connection?  I have never done anything like this before.
0
Comment
Question by:sharris_glascol
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 6
17 Comments
 
LVL 84
ID: 40451163
You can create an ADO connection like this:

Dim con As New ADODB.Connection
con.ConnectionString = "Provider=SQLNCLI11;Server=GCERP;Database=GC_Emp_data;Trusted_Connection=Yes"
con.Open

You could then use the "con" object to open a recordset, for example

If you must use a UserID and Password, you'd use syntax like this:
con.ConnectionString = "Provider=SQLNCLI11;Server=GCERP;Database=GC_Emp_data;Uid=UserName;Pwd=Password"

Note the Provider might be different, depending on the version of SQL Server. See this website for many examples of connection string syntax: https://www.connectionstrings.com/
0
 

Author Comment

by:sharris_glascol
ID: 40452243
So do I put just in place of all the other code I had?  How do I point to a table?
0
 

Author Comment

by:sharris_glascol
ID: 40452264
This was the code I was using but gave me error at the .open

Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   'Create a new ADO Connection object
   Set cn = New ADODB.Connection

   'Use the Access 10 and SQL Server OLEDB providers to
   'open the Connection
   'You will need to replace MySQLServer with the name
   'of a valid SQL Server
   With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = "GCERP"
      .Properties("User ID").Value = "sa"
      .Properties("Password").Value = "Gcdown1"
      .Properties("Initial Catalog").Value = "Gc_EMP_Data"
      .Open
   End With

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = "select * from Posistions"
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 84
ID: 40452309
gave me error at the .open
Please always provide information about the error. Posting something like that is akin to telling your auto mechanic "it won't work" and walking away :)

If your goal is to just bind a form to the table, then the simplest way is to "link" that SQL Server table to your Access program, and then create the form as you would any other Access form. Creating a Recordset and setting the Form's Recordset property to that object can be done, but in general there's not much reason to do so.

That said: In order to link a Form's Recordset to a Recordset object, the Recordset object must use a ClientSide cursor.
0
 

Author Comment

by:sharris_glascol
ID: 40452327
Yes sorry about the vague answer..  What I am trying to do is setup this database that I am going to deploy out to the office.  I have my tables in Sql.  Right now my machine has them linked via a ODBC connection but I don't want to have to go around to each machine and setup those connections.  So do I still leave the tables linked and the On Load it will connect to the Sql server with the connection String?
0
 
LVL 84
ID: 40452349
Did you create a DSN to do this? If so, you'd have to deploy that DSN to each user (or create a new DSN for each user), and the users would be able to use your application as-is.

I find it easier to use DSN-less tables, and setup the connection string for my linked tables on startup. Takes a little more work on your part, but overall it's fairly straight forward.

Armen Stein has a neat relink utility here: http://www.jstreettech.com/downloads.aspx (the JStreet Relinker). That's pretty much drop in code for your needs.
0
 

Author Comment

by:sharris_glascol
ID: 40452579
Ok I am overly confused..  I have a database that I have created Linked tables on my pc that I created an ODBC connection to GCERP. Everything works great from my pc.  To get the database to work for other users, do I leave the linked tables in the database and just add the adodb connection to on page load?  Sorry very confused ...
0
 
LVL 84
ID: 40452731
No - you would leave things as they are now, and you would "relink" the tables when needed. In many cases, you'd relink each time the application starts, so something like the JStreet relinker (above link) would be what you need.

Here's another resource for DSN-less connections: http://www.accessmvp.com/DJSteele/DSNLessLinks.html and http://support.microsoft.com/kb/892490. Those should shed a bit more light on what you're trying to do.

If you don't need that, then you could always teach users how to use the Linked Table Manager (assuming they have full versions of Access, of course).

Or, you could base your connections on a DSN, and create that DSN on each user machine where your application needs to run. That would be a bit of a task, however, if you have more than a handful of machines.

Changing to ADO and Recordset-based forms would be a huge task at this point. I'd not consider that option at all ...
0
 

Author Comment

by:sharris_glascol
ID: 40452838
Thanks for the help.  so on the Macro do I have to setup one for each table. and do a call to that macro on the on load?
0
 

Author Comment

by:sharris_glascol
ID: 40452915
ok i have created a macro with this code:

Option Compare Database

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(dbo_Positions As String, Positions As String, GCERP As String, GC_EMP_DATA As String, Optional sa As String, Optional Gcdown1 As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String
   
    For Each td In CurrentDb.TableDefs
        If td.Name = dbo_Positions Then
            CurrentDb.TableDefs.Delete dbo_Positions
        End If
    Next
     
    If Len(sa) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & GCERP & ";DATABASE=" & GC_EMP_DATA & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & sa & ";DATABASE=" & GC_EMP_DATA & ";UID=" & sa & ";PWD=" & Gcdown1
    End If
    Set td = CurrentDb.CreateTableDef(dbo_Positions, dbAttachSavePWD, Positions, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:
   
    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

-------------------
Then in the form_position onOpen I have put this
    If AttachDSNLessTable("authors", "authors", "(local)", "pubs", "", "") Then
        '// All is okay.
    Else
        '// Not okay.
    End If
End Sub

I am getting a Compile Error:  Expected variable or procedure, note module. and it Highlights the name AttachDSNLessTable on the form code.  Any ideas?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40452940
ok i have created a macro with this code:
Do you mean you've created a VBA routine with that code? That's what you should do:

1. Create a new Standard Module. Name that Module something like "modRelink"
2. Copy and paste the code you found above into that module. Do NOT change anything - just copy it directly. The starting line of that function should read:

Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

Open in new window

Now, in the Open or Load event of your startup form, you'll have to call the AttachDSNLessTable. You'd have to do this for each table that needs to be reconnected. If you just have a few, you can call them directly:

AttachDSNLessTable("YourLocalTable", "TheSQLServerTableName", "GCERP", "GC_EMP_DATA", "", "")
AttachDSNLessTable("YourSecondLocalTable", "TheSecondSQLServerTableName", "GCERP", "GC_EMP_DATA", "", "")

and so on. If you have a lot of tables, you could create a new, local table in the Access database and store the Local and Remote table names, and then open a Recordset and loop through that. In that loop, you'd call the above code, which would relink your tables. The first link I provided shows how to do that.

Note also that the above calls assume you're using a TrustedConnection to the SQL Server. If you must enter a username and pass to log onto the SQL Server, you'll have to include those in the last and next-to-last arguments of the AttachDSNLessTable.
0
 

Author Comment

by:sharris_glascol
ID: 40453011
OK so I create a Macro and Copy and paste the code from the site you sent me and I did't change anything. I saved the macro to modRelink. I started this with the string you attached.


Then in my form i goto the OnOpen and attach the AttachDSNLessTable("dbo_Positions", "Positions", "GCERP", "GC_EMP_DATA", "username", "Password")  
End Sub

But it I am getting a Compile Error:  Ambiguous name detected: AttachDSNLessTable..  any ideas?
0
 

Author Comment

by:sharris_glascol
ID: 40453021
On the Main Code I pasted do I not go in and change the StLocalTableName, stRemoteTableName, .....??
0
 

Author Comment

by:sharris_glascol
ID: 40455115
Thanks for all the help yesterday.  I got it working, Can we do the same thing with a view in sql?
0
 

Author Closing Comment

by:sharris_glascol
ID: 40455116
Was Very Helpful.
0
 
LVL 84
ID: 40455121
Can we do the same thing with a view in sql
Not sure what you mean by that. Can you explain further?
0
 

Author Comment

by:sharris_glascol
ID: 40455144
In sql I have a view.  Can we use this same code to link to a view? or does this only work with Tables.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

751 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