Solved

Connect Access UI to SQL DB

Posted on 2014-04-17
4
527 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 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

685 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