X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Forms And Dsn-less Recordsets, Access 2016    
post May 31 2020, 09:43 PM

Posts: 32
Joined: 1-February 18

Thanks all in advance for your help.

I'm converting a front-end/back-end app to a SQL server backend. I'd like to use DSN-less connections. I've got the code worked out to connect to the dataset once the form opens but it's a read-only set.

I basically have 2 questions:

1. are DSN-less sets updateable or are they like pass-thru queries and read-only?

2. Does anyone have a primer they can direct me to or advise on how to approach this issue?

Code I use to stock the form with data on open that produces a read only set:

Private Sub Form_Open(Cancel As Integer)
Call loaddata
End Sub

Sub loaddata()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim conn As String

conn = "Driver={SQL Server Native Client 11.0};Server=localhost\sqlexpress;Database=northwinds;Trusted_Connection=yes;"

vsql = "select * from employees;"

Set db = OpenDatabase("", False, False, conn)
Set rs = db.OpenRecordset(vsql, dbOpenDynaset, dbSeeChanges)

Set Me.Recordset = rs

End Sub

Thanks again!
Go to the top of the page
post May 31 2020, 11:38 PM

Posts: 29
Joined: 7-September 15

I just finished overhauling an entire project to implement a DSN-less connection using an ODBC connection string to an Azure backend. All-in-all I think it turned out pretty well. I can't say for sure if it would be the defacto way to approach it, but I can certainly give you some insight into what I did to achieve functionality.

I ended up building out a 'directory' to handle pass-through queries. This would handle your CRUD operations as needed. The advantage of doing this is to force the server to do the heavy lifting and to take away the painfully slow JET handling of queries. If you're working with a database that is over WAN, I would recommend you set something like this up too, otherwise you'll find extreme latency with your application. Ultimately the setup would depend on your data requirements. Mine required some organization of different datasets (lookups for dropdown lists, filtered datasets for continuous forms, single record retrieval, pass-through executions of sprocs for calculated updates, etc.) managed in a way that could be maintained if need be.

It might be a bit difficult to outline how I put this together here. Feel free to DM me if you want to discuss it in more detail.

Go to the top of the page
post Jun 1 2020, 04:09 AM

UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada

are DSN-less sets updateable or are they like pass-thru queries and read-only?

They are updatable. A DNS less connection is a standard plane Jane linked table to SQL server.

You should not have to change the form, or even re-write VBA code. Linked tables are DSN less by default.

If you having to re-write your forms code then you are doing something VERY wrong here.

Does anyone have a primer they can direct me to or advise on how to approach this issue?

You don’t’ really have to do anything more then use the built in linked table manager.

Access for 20+ years has DEFAULTED to dsn-less connections.

You don’t need VBA code, or even some special re-link code.

ALL YOU have to do is link to SQL server. If you use a “FILE” DSN (and that is the default that the ODBC panel suggests) then you will now have a DSN-less connection.

So, you don’t need any code for DSN-less connections.

And you should not have to really change code in that form that worked before. All you do is link the tables to SQL server.

Access by DEFAULT creates and uses dsn-less connections. All that is required is that when you used the built-in linked table manager is to ensue you use a FILE dsn.

Access automatically converts a FILE dsn into a DSN-less connections for you.

Once you linked the tables, then you can even delete the original DSN you created.

And when you deploy the application (the front end) to other workstations, you will not need to setup a DSN or do anything.

Code I use to stock the form with data on open that produces a read only set:

I am somewhat confused here? I don’t see why you would be writing code to load the data into the form? Did you have such code before? (If yes, then it should work fine).

So, there is no need to write or introduce NEW code to load data into a form. It not clear if this was how your application worked before, or if there is some assumption here that you now need to write new code (you do not).

Access will work fine with a simple linked table – just link it to SQL server the same like one did in the past when linking to an access back end.

There should be ZERO reasons to start writing new code here.

If you have a table bound say to a SQL server table. Standard linked table.

Let’s say it has 1 million rows.

So, in access in the past? Let’s work on say one invoice.

So, you in the past would do this:

Docmd.OpenForm "frmInvoice",,,"InvoiceNum = 123456"

The form will ONLY pull the ONE record down the network pipe.

So in summary:

Access by default uses DSN-less connections. All you have to do is make sure you choose FILE dsn when linking the table. There is no VBA code or anything else required here. Just link the tables.

Once you link the tables, then all of the forms that in the past should now work just fine.

The only change that you need to EXISTING VBA code for recordsets?

If you had this:

Dim rst    as DAO.RecordSet
Dim strSQL  as string

strSQL = "select * from tblHotels where City = 'Eddmonton’"

set rst = Currentdb.OpenReocrdSet(strSQL)

If you linked the tables to SQL server? Well, then you need to do a global search and replace – find any above OpenRecordSet code, and add this:

set rst = Currentdb.OpenReocrdSet(strSQL, dbSeeChanges, dbOpenDynaset)

So, I put the dbSeeChanges, dbOpenDynaset in my paste buffer.

I find that even in a rather “large” application, finding the open reocrdsets as per above only takes about 5-10 minute tops.

However, all the forms and reports, VBA code etc you had before should work and run just like it did before.

So, I not really sure if you need some kind of tutorial on DSN-less connections. All you do is use the linked table manger and just make sure you used a “FILE” DSN, and you now will have a full operational application works and runs as dsn-less.

I might be missing some huge whopper of a issue here, but how your application worked with linked tables to a access back end, or how it will work with a SQL server back end is really the same.

If your design in the past was limiting data pulled into a form, then it should do the same now.

I mean, surly in the past one had designs that avoided just opening up a form to a huge table. You THEN have the user do some ctrl-f to search though a massive amount of data. That would be slow with SQL server or with a access back end.

But, you no doubt provided some prompt or means for a user to enter say a customer number or invoice, and then you LAUNCH the form to the ONE record.

As I stated, a plane jane access form, bound directly to that linked table (Access back end, or SQL server back end), will ONLY pull down over the network pipe what you provided in the “where” clause that you “no doubt” had in the past

Access will respect the where clause, and ONLY pull down the record(s) you tell the form to load – and in most cases I would assume that was a “simple” where clause.

That where clause and simple approach will work just as well with SQL server, and even bound forms directly to the linked table will only pull the one record based on the where clause.

So, there really not a lot of change or learning here. DSN less connections are automatic and the default in Access. And you don’t need any special VBA code or even custom re-link code to enjoy and use dsn-less connections. It quite much the default for access.

The only requirement here is to ensure you use a FILE dsn when you link the tables. Access converts this to dsn-less for you.

Edit: How much work to make your application perform well will depend on how well of a job you did in the past to restrict records loaded.

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
post Jun 1 2020, 07:04 AM

Posts: 4,702
Joined: 11-November 02

QUOTE (lancemcgonigal)
...I've got the code worked out to connect to the dataset once the form opens but it's a read-only set...

Do your Tables all have Primary Keys, or at least Unique Indexes, defined for them? Is the PK part of the RecordSource of the Form? I believe I'm right in saying that if not, the Forms will be Read-Only using ODBC.

Linq ;0)>

BTW...The Monkey is laughing at me...not at any other poster!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
post Jun 1 2020, 07:10 AM

UA Admin
Posts: 37,462
Joined: 20-June 02
From: Newcastle, WA

Albert's given you the detailed account of this, but I want to make sure we're clear on why you want to bind a DAO recordset to a form in the first place. Did your previous accdb/accdb design rely on unbound forms this way? If so, why?

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Jun 1 2020, 07:29 AM

Posts: 32
Joined: 1-February 18

Thanks all for the rapid replies.

I'm trying to secure the app as much as possible without going into full-blown SQL security. The data is not that sensitive but I don't want folks to get behind the forms or get a query in design mode. I'm already distributing it in a .accde format.

I had seen a video where the author had cited security as a reason to use DSN-less sets and binding at form load. This seemed ideal until I couldn't edit or add/delete the set.

For some background, we're moving the app to a SQL Database on AWS so that users can work in the client while at home. I'm thinking that instead of using the DSN-less sets now that I may disable the shift bypass and deploy that solution.

It seemed that the DSN-less set would allow me to not have my tables in the database window and use them too. What do you think?

Thanks again. I really appreciate the feedback here. You guys have gone WAYYY out of your way to help.


MissingLinq found my issue...In my zeal to push the tables to SQL it didn't inherit the PK. I went back and set the PK and Edits/Adds/Deletes are available. Again...Thanks to all of you for your suggestions.
This post has been edited by lancemcgonigal: Jun 1 2020, 08:06 AM
Go to the top of the page
post Jun 1 2020, 12:24 PM

UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada

Sounds all good.

No, the DSN-less issue and that of having linked tables are not really related to each other.

However, I do suggest that you adopt a nice security trick – and that is to link the tables without the uid/password.

The reason for this is simple:

If the user does/did shift by-pass into your application? Well then if they get to the nav pane, and click on, or try to open a linked table? It will not work.

Or if they fire up another copy of access (and thus you can’t control the shift key or use of the nav pane).

So, now if they import your linked tables? Once again, if they click on them, or try to open them, the links will not work.

So, DSN-less has little or no relationship to the “fact” or “need” of linked tables – they are separate issues.

However, by linking the tables without the uid/password, then your security is increased by significant amounts. If a user were to by-pass your start-up code (and thus your code executing a SQL logon does not occur), then the linked tables, PT queries (even ones that call store procedures) will not work, and not have use of SQL server.

The trick thus involves that in your start-up code you execute a one-time SQL logon. Once you executed this logon, then the linked tables will work. (And so will the PT queries etc.).

But, if a user does a shift bypass (which I assume you disabled), or a person launches full access and imports the linked tables, those table links will NOT work.

It also far more secure because you not storing the uid/password in the table links which are in plain text.

Another bonus? If you wanted to have users logon on application start-up? Well, you can prompt for a uid/password – execute the logon, and thus DIFFERENT users can logon, but you don’t have to re-link the tables!

So, this approach means that different SQL users can be logged into the system, and all this works without a table re-link.

So, now, deploying a compiled accDE with the shift key bypass disabled means your application is locked down quite nice. But even better is that if they are somewhat experienced users of Access, and launch full version, and import your linked tables, they will STILL not be able to use them!

So the above tip is certainly a benefit of DNS-less linked tables.

I do strong suggest adopting the “one time” logon to SQL server in your start up code. If users manage to by-pass your start up code, then they can’t use the linked tables.

The code for a one time logon is this:

Function TestLogin(strCon As String) As Boolean

   On Error GoTo TestError

   Dim dbs          As DAO.Database
   Dim qdf          As DAO.QueryDef

   Set dbs = CurrentDb()
   Set qdf = dbs.CreateQueryDef("")

    qdf.Connect = strCon
    qdf.ReturnsRecords = False

    'Any SQL statement will work below.
    qdf.SQL = "SELECT 1 FROM FakeZoo"

    TestLogin = True
    Exit Function

    TestLogin = False
    Exit Function

End Function

The above is really all you need to make this work, but a full explain and code is outlined here:

Power Tip: Improve the security of database connections


Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada

Go to the top of the page
post Jun 1 2020, 07:17 PM

Posts: 32
Joined: 1-February 18

Albert...That's a great idea. I think you may have changed my course of thinking on this topic. I'm going to test a few things and lean your way on this. Thank you!
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    6th July 2020 - 12:51 PM