Solved

Connect Access UI to SQL DB

Posted on 2014-04-17
4
504 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
  • 2
4 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
vba DCount with 2 criteria 3 34
Field behavior for "locked" form 12 29
data analyst 3 48
MS Access Memo (Long Text) Field Not Exporting Correctly From a Query 13 41
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…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
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…

911 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

21 Experts available now in Live!

Get 1:1 Help Now