19. (Advanced Programming In Access 2013) Connecting Form Objects Directly To SQL Server

[@james6164]

You are a really good teacher. Why didn't I find your channel a long time ago???

[@paulsobel67]

Just to be clear, to bind the SQL query to a subform, you have to use ADO. I got this to work with ADO but, as all the comments below, it looks like when I try ADODB, it will not work. These are GREAT videos, I am glad I found them, good job.

[@nutsonbikes]

Hello @paulsobel67, What do you mean? I am slowly going crazy... I am moving my Access backend to SQL Server and have watched this great series. So I embarked on testing the connection methods. I made a simple table in SSMS, connected successfully with linked tables, connected successfully with DAO, I have even connected successfully with ADO... but here's where I get stuck. I have connected to the test table, populated the recordset in VBA (tested it with Debug.Print), but something weird is happening when the "Set Me.Recordset = rs" runs. Absolutely fine with DAO, but the ADO routine keeps showing the #name? error in the forms text boxes. It doesn't matter whether I set the control source in design view or VBA!

[@nutsonbikes]

Trawling some forums, I found that you need to explicitly state rs.CursorLocation = adUseClient!!! 🙈

[@3wsystems663]

it is possible to Edit and Modify the Active Orders directly from de FORM like access tables?..

[@ianzhang8118]

Great tutorial, shall recordset need close or set to nothing?

[@swapnilwankhede3440]

Hi Steve Excellent Tutorials, Thanks a lot Steve for uploading this videos, it’s really helpful, never found such good videos.

[@swindersingh3280]

Steve Sir, thanks.

[@arquivoxjk]

Congratulations from Brazil..., excellent video

[@richardstephens2910]

This is all very fine for displaying results in Datasheet view only BUT how do you build a continuous Form when you have no physical data in the database to base the form controls on ? I'm trying to avoid using ODBC.

[@jooryalhamed938]

It seems like using ADO instead of DAO doesn't work; gives #Name error

[@jandel2k]

hello i have tried doing set me.subforms.form.recorset = rs mine doesnt work...what would be my problem. thanks

[@EsmerinoNeto]

Hello. One time the DAO connected with SQL Server (by unbound form), how it is possible change/update the form fields values? The Form Recordset is unable to update.

[@catulopsae]

Does users using the access db need access to sql server?

[@jrsam5075]

Hi, I am trying to use SQL server as backEnd. I have my front end as ms access. I have a form named Customers Sales Add Only + a subform where the details of sales is being recorded. I don't know how to make it work with SQL Server. Don't know how to proceed. Can you help me Steve?

[@Leonardo_A1]

Hi Steve i plan to bring my access appl to the web and mobile using power app and CDS. It will be great if you can create some videos to bring access data to mobile phone? Thanks a lot Leonardo

[@EftyEdge]

Hi Steve, awesome series! Very helpful and professional!!! Thanx for your effort... :) Maybe you can give me an answer to following problem on the basis of your daily work. Is it better to connect or bind an MS-SQL ADO recordset to a Form and then work with the recordsets of the form to fill an object. I think thats the way you described in your video... Or is there an advantage to use an unbound recordset and fill every object ˋmanuallyˋ by using ME.FIELD = ADORec("value")...!? Thanx in advanced. Kind regards... Efty

[@santabanana]

Hi Steve. When binding a form with either and ADO or DAO recordset (opened from a SQL Server), the form is losing the filtering capability (This means that the context menu for filtering on the form does not display the records to be filtered). I have read that ADO recordsets don't allow the form to have the form filtering functionality. However, DAO should work. Do you have any insight into this issues?

[@ProgrammingMadeEZ]

I don't even use filters anymore because you can filter with the SQL string.

[@santabanana]

That is the approach that I thought I would need to use. I just wanted to use the built-in filtering functionality in Access instead of having to change the SQL string. I had no issues with this before upsizing my database to SQL Server (all data was in tables in the accdb file). Thanks Steve for you quick reply! Great videos; they have been a great help!

[@cookiewl]

I’m getting an error can’t find file dbo.mdb I suppose since this is a 9 year old video there have been some changes. I’m using SQL Server Express 17 and MS Access in 365.

[@ermacheton]

Steve, do I have to open a connection to the database every time I want to link record source of a form? can I just open the database once in my application and work out of it? like have an openDatabase sub on a module and call it once and have that connection open throughout? if yes, how would that be? thanks so much.

[@eveinc_programmer2960]

Hi! Great video! Curious question: if I have two record sets (rs1 and rs2) (either DAO or ADO recordsets) that I've already populated with data in VBA, is there a convenient way to programmatically combine them in VBA? So something like "select rs1.field1, rs2.field2 from rs1 inner join rs2 on ...etc." Granted that it would have to be the Access engine that deals with the query, but it seems like it would be a handy thing to be able to do. (or maybe even just running a select "filter" against rs1 kind of thing?) If this was covered in another video, a link would be great. [I looked for such a topic, but haven't found it yet.] Thanks in advance!

[@kenc8411]

Can I do the same if I have MySQL as backend?

[@teetayo31]

Nice videos, in this video I understand you had to remove references to Access based Table/Query from the SQL String to be used for the recordset because it will be run on ms sql server and not access db engine. In a scenario whereby there is dire need to use a table or query that resides in access as part of the SQL String (sql) criteria expression or join, alongside the main ms sql server table. I mean, what will be the alternative best solution if you were not to remove/erase the parts that reference the access db based query (Order Price Total) in this video. Thanks

[@janezklun]

Great video!

[@josejorgeduran3061]

Clear and ilustrative, thanks and congratulations; I just have a question: in the example you defined the RecordSource from another forms with linked table.... Can the RecordSource set to null with this unbound form? ......(sorry my very bad english)

[@ProgrammingMadeEZ]

+Jose Jorge Duran Yes you can set the recordset to null.

[@josejorgeduran3061]

Programming Thanks a lost, I'll try it and probe

[@aliahmed16314]

Hello Steve, thank you very much for this great information. I have some questions if you can help .. when i delete the employee table, the combo box lost its list so do i need to fill it by writing connection string and its query? and how can i write the connection string in a public class to call it? and can i connect to another database type such as access database rather than sql server? thank you very much.

[@ProgrammingMadeEZ]

Ahmed Kassim Is the employee table on the SQL Server?

[@aliahmed16314]

Programming Yes, it is on SQL Server, Thank you Steve for replaying.

[@ProgrammingMadeEZ]

Ahmed Kassim Then it sounds like your rowsource is still being used to try to populate the data for the combo box. Use the technique from this video to set the recordset property of your combo box to the employee table on your SQL Server.

[@aliahmed16314]

Programming OK, the code should be in the "Form OnLoad event??"

[@ProgrammingMadeEZ]

Yes. The code to add the recordset to the combobox should be in the onload event.

[@حيدرالحداد-غ1ف]

Excellent

[@craigham6907]

Thanks to you and your videos, I now have my recordset defined using SQL. Now, I need to set different specific row sources on a form using my SQL statement. How do I go about doing that? Do you have a video that covers it? If so, I haven't found it yet. Thanks in advance.

[@craigham6907]

I had it working using code similar to that for setting the recordset...but then I closed the form and when I reopened it, it no longer works. Help!!!

[@ProgrammingMadeEZ]

Make sure you are setting the SQL query when the form opens too.

[@craigham6907]

I am...but it's not working.

[@ProgrammingMadeEZ]

Without knowing your code there is no way for me to tell you what's wrong.

[@craigham6907]

That's one problem I'm having...I'm not 100% sure what code is used to set a row source. Basically what I've done is dim db as a DAO database and set it equal to my opendataset, like was done for setting the form's recordset. I then set my combo box row source to my SQL statement and then requery the combo box. I did this in it's own private sub which I run on form current, load and open. When the form is accessed, the record source for the combo box is what I set it as, but nothing shows up in the actual combo box. What am I missing?

[@vibhaskashyap8247]

i am unable to connect to the SQL server database using the below syntax Set db = OpenDatabase("", "false", "false", "Driver={SQL Server Native Client 11.0};Server=Localhost;Database=Sample123;Trusted_Connection=true;") can you please tell what is wrong here

[@ProgrammingMadeEZ]

+Vibhas Kashyap The server may also need to have the service instance like Server=Localhost\SQLEXPRESS

[@craigham6907]

OK...I think I have my other issues ironed out (fingers crossed). My issue now is that when other users try to open the application (that I've packaged through SSE), they get runtime error 3151. All connections in the database are set up as "DSN-less", and I'm using the trusted connection method you covered. It works fine for me, but I (and my IT folks) can't figure out why it's not working for others. Any ideas? Thanks.

[@ProgrammingMadeEZ]

Is there a specific line where you get the error?

[@craigham6907]

It comes in on my 'Set db = OpenDatabase ()' line.

[@ProgrammingMadeEZ]

If it works for you but not for them, it has to be network or security related.

[@craigham6907]

We've tried making the user a local admin, setting ODBC data sources, and setting specific permissions on the server...but nothing has worked. Only one other user has been able to log in, and that was our IT guy using Access Runtime 2013.

[@ProgrammingMadeEZ]

It wouldn't be permissions on the local box, it would be permissions to the server. In your connection string are you passing a username and password or did you set it to use Windows Security? If you are using Windows Auth, then that user needs to have their permissions set on the SQL Server to access the database.

[@Rhythm515]

Steve, as always your vids are top drawer ~ this one is no exception - Just one question, are the controls you're working with bound or unbound? In order for this method of populating a form to work - what should the form properties be set to? I am desperately trying to rid my app of linked SQL tables and your method seems like the ticket but I cannot get it to work - at all - I'm getting an error on the: Set Me.Form.Recordset = Rs. The form I'm working with is the main form (my Sub Form is bound by parent/child link) and all the controls are bound. I have changed the above Set Statement in just about every way I can think of and I still get either an Object or Variable or Invalid Property error. Anyway, if you can offer any advice for someone with my circumstance that would be great - And, could you try to always include form & control property settings when working with these objects it's critical information and would be immensely helpful - Thanks Steve ~

[@swindersingh3280]

A great heart !!!!

[@altonbrisbanejr8254]

Is there a reason you went with DAO instead of ADO?

[@ProgrammingMadeEZ]

+Alton Brisbane Jr I get into DAO vs ADO in the advanced series... but the short of it is DAO is what Access is built around.

[@schalld1]

i would like to see this in ado also.

[@Shallo9]

Hi Steve, Greetings! I'm struck with something. Your connection string works fine, but I'm having to create a login for every user on SQL Server. I was just wondering if there is a way where I create a generic Shared Username on SQL Server and pass that details into the Connection string so I don't have to create a separate login for each user. Can I do something like MyConnectionString = "Driver={SQL Server Native Client 11.0};Server=WS123456\SQLEXPRESS; Database=ActivityRecorderBE; User Name = ARUser; Trusted_Connection=yes;"" PS: where ARUser would be a generic shared Username. Similar to something i did in .Net as below. Public Function getConnectionString() As String Dim govQAClient As New SqlClient.SqlConnectionStringBuilder With govQAClient .DataSource = "ServerName" 'replace with your 'server name' .InitialCatalog = "DatabaseName" 'replace with your database name .IntegratedSecurity = False .PersistSecurityInfo = True .UserID = "Username" 'replace with the sql server login .Password = "Password" 'replace with the password Return govQAClient.ToString() End With End Function

[@ProgrammingMadeEZ]

Well, it would be better if your SQL Server was part of a domain and you use Windows Logins to manage user access to the SQL Server. Other than than, you can dynamically create the connection string, not with a string builder but just by concatenating the parts together. "Driver={SQL Server Native Client 11.0};Server=" & myServerAddress & ";Database=" & myDataBase & ";Uid=" & myUsername & ";Pwd=" & myPassword & ";"

[@nekkdo891]

First of all let me thank you for these excellent video series you are making. What about if my Access database is not connected to SQL server but to SharePoint server? Is there a way of doing that same thing with the VBA code?

[@ProgrammingMadeEZ]

Tomaž Mazi As long as there is a driver with a connection string you can use, yes. A cursory search revealed to me that there are Sharepoint connection strings like this one: Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=1;RetrieveIds=Yes; DATABASE=sharepoint.yourdomain.com/Documents/;LIST={5999B8A0-0C2F-4D4D-9C5A-D7B146E49698}; It looks to be using the Access DB engine to connect to the Sharepoint which is a bit unusual, but whatever works!

[@bradcathyquade3134]

This approach looks very elegant, but I must be doing something wrong. I've modified the code as follows: Set rs = db.OpenRecordset(mySqlString, dbOpenDynaset, dbSeeChanges) If Not rs.EOF then rs.MoveFirst Do Until rs.EOF Debug.Print rs!id, rs!accession_no rs.MoveNext Loop Label1.Caption = "my record count is " CStr(rs.RecordCount) Set Me.subFormBlockInfo.Form.Recordset = rs 1) When I run it as above, it iterates through the 160 test records, reports that RecordCount is 160, and then gives error 2467 The expression you entered refers to an object that is closed or doesn't exist. 2) When I comment out the Do...Loop, I only get RecordCount = 1, before getting the same error. Why doesn't recordset rs return all of the records? Why is the subform broken?

[@HarveyFrench]

You don't need to requery after setting the recordsouce as it's done implicitly. kzbin.info/www/bejne/ZoLMkHh9ntmBns0

[@ProgrammingMadeEZ]

Harvey French This has not been my experience.

[@HarveyFrench]

I tried this. I got get 1 then 2000 in the immediate window. Mind you tblclient is a local access table. Dim rs As Recordset Set rs = CurrentDb().OpenRecordset("SELECT * FROM tblClient") Debug.Print rs.RecordCount rs.MoveLast Debug.Print rs.RecordCount

[@HarveyFrench]

I think prior to Access 2010 you had to do a requery. Obviously using it can cause a double database hit.

[@ProgrammingMadeEZ]

Harvey French If this has been fixed for 2013, it must be only with 2013 database versions. A 2007-2010 database running in 2013 will still have the same re-query need... hence why at least for this demonstration with a 2007 Northwind database file it is still needed. You could very well be right for anything brand new built in 2013.