Solved

Connect Access UI to SQL DB

Posted on 2014-04-17
4
501 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

757 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

19 Experts available now in Live!

Get 1:1 Help Now