Improve company productivity with a Business Account.Sign Up

x
?
Solved

Connect Access UI to SQL DB

Posted on 2014-04-17
4
Medium Priority
?
640 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 2000 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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

584 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