![]() ![]() By the way, these procs are created automatically when you successfully login to SQL Server so they will run without error when selected in the Demo application. The key part in the above script is the QueryDef.ReturnRecords property, which must be set to TRUE when selecting records and FALSE when executing a script to create and/or alter database objects. ' (ProcText contains the script to create the stored proc.) "where Name='" & rst!ProcObjectName & "'" SSQL = "select count(*) as ProcExists from sysobjects " & _ ' Loop through the list, check for existance and create if needed. ' Create a querydef object and set its CONNECT property. Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot) SSQL = "select ProcObjectName, ProcText from tblSQLProcList" ' Open a recordset of the stored procs to be used, and tested for. The next step in our process is to test for their existence and create them if they are missing. The metadata for these procs is stored in a local Access database, including the script to create each of them. The sample application (shown below) leverages four existing stored procedures from the Pubs database, and two new ones that I created. That is my preference, so I will not cover DSNs in this article. One might suggest that a DSN be used in place of dynamic parameters, but I have found that it is more clumsy to set up user desktops with a new DSN than it is to simply allow them to enter login credentials from the application and save them for subsequent logins. While I will not take the time to list the code for that here, it is included in the download. StrConnect = "ODBC DRIVER=" _Īs you can see from the screen shot, the code also allows you to read connection information from an ini file. ' Password may be NULL, so provide for that possibility MsgBox strMsg, vbInformation, "Missing Data" StrMsg = "Enter name of your company's Server." & _ ' Check for existence of Server, Database and User Name. You will need to test each one for existence, except for the password, which may be blank, although you should be scolded if you allow blank passwords. If you name your text boxes well, the code will be very easy to read. The premise is simple: collect login parameters, test validity and relink local tables and Queries. In fact, that’s where I started when preparing the code for this article, so if you’ve already read that article, this login screen will be familiar to you. To read it for yourself, follow this link: ODBC DSN-Less Connection Tutorial I wrote an article about this process about four years ago and it is still available here at Database Journal. The first step, if your application has not already managed it, is to collect and apply the login credentials to your local DAO table and/or query objects. The best way to understand the process is to step through the code in debug mode and watch as it happens, but in the mean time let’s look at some of the more key code scripts. You will need access to the Pubs database on a Microsoft SQL Server, including the necessary permissions to read the sysobjects table and create stored procs. The download for this article contains all the code from the article in both Access 97 and Access 2003 versions. ![]() Display the results of the query output in a listbox.Build dynamic SQL Pass Through query with parameter values.Check for existence of stored procs and add new ones where necessary.How to login to SQL Server from Access and refresh object connect properties.In addition, we’ll cover questions like these: The following article will demonstrate a simple method for executing procs from Access. The simplicity and popularity of SQL Server means that more and more developers who build applications with Microsoft Access will want to learn how to take advantage of server side processing using SQL Server Stored Procedures. ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |