Solved

Connect Access UI to SQL DB

Posted on 2014-04-17
4
535 Views
Last Modified: 2014-04-18
Hi Experts,

When I connect an Access UI to a SQL DB, I usually first create a DSN on each workstation through Control Panel, then connecting Access to the DSN?

Is it possible not to use a DSN, but maybe store all credentials on a local Access table, like server IP, Database, User & Password?  

If this is possible, then how, and will I be able to see the SQL tables, their contents, and bind to them as I normally would in Access?

Thank you.
0
Comment
Question by:APD_Toronto
[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
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40008070
0
 

Author Comment

by:APD_Toronto
ID: 40008092
I thought a few lines of code I do in startup, the link goes into various of procedures.
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 40008254
Ok, here is how I accomplish in code what you are asking ... basically a DSNless connection.  The sample below is using MySQL server as the back-end db server but the concept will be the same.  You just need to specify the SQL Driver instead of the MySQL driver in the connection string as noted below in the code.   Let me know if you are not clear on this part.

1.) First create a 1 record SetupGeneral local table in your Access front-end app. with fields to store your SQLServerHost Name, SQLServerDatabase Name, UID and PWD.

2.) Create a second local table called LINKED_SERVER_TBLS with fields for LINKED_TABLE_NAME AND DESCRIPTION.  This will contain all of the SQL Server tables that you want to link up to.

Then, use the following code in your start up form to link the tables.  The code will first check to see if the table is already linked, if so, it will delete the object then relink it using the current parameters stored in your SetupGeneral table.

Function LinkMySQLTables()
Dim strMessage As String
strMessage = SysCmd(acSysCmdSetStatus, "Linking MySQL Server ODBC tables, please wait ...")
DoCmd.Hourglass True

Dim db As Database
Dim tdf As TableDef
Dim rst As Recordset

Dim strMySQLServerHost As String
Dim strMySQLDatabase As String
Dim strMySQLLogin As String
Dim strMySQLPswd As String
Dim strMySQLConn As String
Dim strSQLConn As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT * FROM LINKED_SERVER_TBLS;", dbOpenDynaset)

strMySQLServerHost = DLookup("[MySQL_SERVERHOST]", "SetupGeneral_Company")
strMySQLDatabase = DLookup("[MySQL_DATABASE]", "SetupGeneral_Company")
strMySQLLogin = DLookup("[MySQL_LOGIN]", "SetupGeneral_Company")
strMySQLPswd = DLookup("[MySQL_PSWD]", "SetupGeneral_Company")



'Build the MySQL Server Connection String
'MySQL recommends using 35 for Access in the "Option=" property
strMySQLConn = "ODBC;Driver={MySQL ODBC 5.1 Driver};Server=" & strMySQLServerHost & ";Database=" & strMySQLDatabase & ";User=" & strMySQLLogin & ";Password=" & strMySQLPswd & ";Option=35;"

'Use this code for SQL Server where you would lookup the values for strSQLServerHost, strSQLDatabase, strSQLLogin and sttrSQLPswd in your SetupGeneral like I did for MySQl above.
'strSQLConn = "ODBC;Driver={SQL Server};Server=" & strSQLServerHost & ";Database=" & strSQLDatabase & ";Uid=" & strSQLLogin & ";Pwd=" & strSQLPswd & ";"


'MySQL SERVER ODBC LINKED TABLES PROCESSING
If rst.RecordCount > 0 Then
    rst.MoveFirst
    Do Until rst.EOF
        If DCount("Name", "MSysObjects", "Name = '" & rst!LINKED_TBL_NAME & "' and Type = 4") <> 0 Then 'The table exist
            DoCmd.DeleteObject acTable, rst!LINKED_TBL_NAME
        End If
        
        Set tdf = db.CreateTableDef(rst!LINKED_TBL_NAME)
        tdf.SourceTableName = rst!LINKED_TBL_NAME
        tdf.Connect = strMySQLConn
        db.TableDefs.Append tdf
        db.TableDefs.Refresh
       
        rst.MoveNext
    Loop
End If


rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

strMessage = SysCmd(acSysCmdClearStatus)
DoCmd.Hourglass False

End Function

Open in new window


Also, if you want to use this code ... to make is easier to follow, just drop the "My" off the variable names through the function and they become strSQL ... etc. etc.

ET
0
 

Author Closing Comment

by:APD_Toronto
ID: 40009336
thank you
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

734 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