[AccessD] Table vs View Recordsource

Thu Jun 24 23:48:19 CDT 2021

Paul,
Interesting. I have never used the Resync or Unique table command. I will have to play around with that, although I think the unique table is only needed if the view has multiple tables. I always have the view return from one table only so as to avoid fiddling around with that and I think it may be more efficient as the sql runs on the server.

SELECT
    CustomerID,
    (Select CustomerType FROM CustomerTypes Where CustomerTypeID =  Customers.CustomerTypeID) as CustType,
    CustomerName,
    Address
FROM
    Customers

Other than that I basically do the same as you with regards to populating combos etc.

I read somehere that using views and opening forms using a where clause is very efficient. I tend to do that these days and spend only time on the forms that load slowly. I find this adequate in my conversion of older adp's that have quite large underlying tables with lots of records giving me a good indication of the speed of the setup. 

Thanks for your time.

Anita 

Anita Smith 
M: 0402 022 462

-----Original Message-----
From: AccessD <accessd-bounces+anita=ddisolutions.com.au at databaseadvisors.com> On Behalf Of Paul Wolstenholme
Sent: Friday, 25 June 2021 14:12
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] Table vs View Recordsource

Anita,

So..
Passing parameters to a user stored procedure in SQL Server and getting one (or more) recordsets passed back is the first step.  I code that as a VBA function in each form and then use it as follows:
    Set rstTemp = FilterRecordSet(1)
    ' 1st result as form recordset
    Set Me.Recordset = rstTemp
    Me.UniqueTable = "ts350ContractConditions"
    Me.ResyncCommand = "EXEC " & Me.Name & "_usp ?"
    'Me.Requery      ' The benefit of this is uncertain
    'This recordset should next be closed (.NextRecordset will prevent error messages when failing to delete a record)

I thought it was getting too messy coming up with the right magic for Me.ResyncCommand, so I reserved the first parameter of my stored procedure to return a single record if the primary key is passed.  It just seemed easier that way.  I name my stored procedures according to the name of the form (which is fine until I rename a form).

I then go on to call the stored procedure a second time to get recordsets for any combo boxes I have on the form.  I assign those as follows:
    ' 2nd result: combo box: SelectContractConditions
    Set Me.SelectContractConditions.Recordset = rstTemp
    Set rstTemp = rstTemp.NextRecordset
    ' 3rd result: combo box: nCompanySites
    Set Me.nCompanySites.Recordset = rstTemp

Perhaps your knowledge of ADPs (that I lack) tells you everything else you need to know.

Paul Wolstenholme

On Fri, 25 Jun 2021 at 15:31, Anita Smith <anita at ddisolutions.com.au> wrote:

> Thank you for the links and information Paul.
>
> Very good to know as I am new to ODBC after working with ADP's for 
> many years. With ADP's I could bind a form to a stored procedure AND 
> supply a criteria - thus moving all the processing to SQL Server and 
> only retrieve one record at the time. This was very fast and efficient 
> and I was gutted when they removed that.
>
> I now have a couple of functions that I call to run stored procedures 
> and also to return recordsets - I use these to do all the data 
> processing and for screens that are not editable I still bind them to 
> stored procedures that I call on when needed. All in all, I thought I 
> had come up with a workable solution but the date problem blew me off my pedestal.
>
> It looks like the ODBC driver is redistributable and so I gather that 
> this is safe to run on my client's computers.
>
> The other link you provided to Access Experts looks very interesting. 
> I have always wanted to delve into moving the data to the cloud and 
> keeping the front end local but I never had the time. The 5 minutes I 
> did spend setting up a database on Azure and connecting to it, seemed 
> to be too slow so I binned it. I guess it's a matter of bandwidth.
>
> Thanks again for taking the time to reply to me.
>
> Anita
> -----Original Message-----
> From: AccessD <accessd-bounces+anita=
> ddisolutions.com.au at databaseadvisors.com> On Behalf Of Paul 
> Wolstenholme
> Sent: Friday, 25 June 2021 07:52
> To: Access Developers discussion and problem solving < 
> accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Table vs View Recordsource
>
> Anita,
>
> I have also been through the SQL Server datetime2() issue with drivers.
> The old datetime type is no longer preferred but the older driver 
> don't know of datetime2() and convert it to a string.
> The SQL Server native drivers solved that issue but they are also out 
> of date now and I presume will eventually trip you up on even newer 
> SQL Server features.
> Now we need separate drivers for ODBC and ADO.  The latter uses 
> MSOLEDBSQL.DLL (OLE DB Driver for SQL Server).  The last time I 
> updated, I got it from
>
> https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driv
> er-for-sql-server?view=sql-server-ver15
> but you should check out connectionstrings.com/sql-server/
>
> You can still connect Access forms to SQL Server using ADODB 
> recordsets so I suspect your ADP history is useful here.  I have never 
> done an ADP but I still find the technique useful as it allows the 
> data-intensive operations to be done in the back end with only results 
> transmitted to the front end (it avoids speed issues especially over 
> VPN links).  I have established my own way of doing this following 
> advice from David Emerson (who also introduced me to this group) and 
> https://accessexperts.com/ led by Juan Soto.  You will need the ADO driver.
>
> Paul Wolstenholme
>
>
> On Fri, 25 Jun 2021 at 00:51, Jim Dettman via AccessD < 
> accessd at databaseadvisors.com> wrote:
>
> >
> >  You want to be using the latest ODBC driver:
> >
> >
> > https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driv
> > er
> > -for-s
> > ql-server?view=sql-server-ver15
> > <https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-dri
> > ve r-for-sql-server?view=sql-server-ver15>
> >
> >  as Microsoft has done work with date/time types between Access and 
> > SQL Server.
> >
> > Jim.
> >
> > -----Original Message-----
> > From: AccessD On Behalf Of Anita Smith
> > Sent: Thursday, June 24, 2021 7:36 AM
> > To: Access Developers discussion and problem solving 
> > <accessd at databaseadvisors.com>
> > Subject: Re: [AccessD] Table vs View Recordsource
> >
> > Hi Stuart,
> >
> > You are a star!!
> >
> > Your question catapulted my brain into action.
> >
> > When first setting up the ODBC Data source  I had a 3 different 
> > drivers to choose from (which I had long forgotten as I did this way
> back last week).
> > Lo and behold, I must have chosen a dodgy one.
> >
> > I have now connected using one of the others and I am back in business.
> >
> > Anita
> >
> >
> >
> >
> > Anita Smith
> > M: 0402 022 462
> >
> > -----Original Message-----
> > From: AccessD
> > <accessd-bounces+anita=ddisolutions.com.au at databaseadvisorscom> On 
> > Behalf Of Stuart McLachlan via AccessD
> > Sent: Thursday, 24 June 2021 19:43
> > To: Access Developers discussion and problem solving 
> > <accessd at databaseadvisors.com>
> > Cc: Stuart McLachlan <stuart at lexacorp.com.pg>
> > Subject: Re: [AccessD] Table vs View Recordsource
> >
> > I assume the BE is SQL Server, please correct me if I'm wrong.
> > What version?
> > Specifically, what ODBC driver?
> > 32bit or 64bit Office and ODBC dirver?
> >
> > On 24 Jun 2021 at 7:58, Anita Smith wrote:
> >
> > > Due to the discontinuation of the humble ADP - I have found myself 
> > > in the dire predicament of being tasked with converting some of my 
> > > projects from ADP to ODBC.
> > >
> > > Much to my horror I can no longer use my stored procedures as 
> > > record sources for my editable forms. I then thought I´d use views 
> > > as I´m thinking they would work reasonably fast.
> > >
> > > Here comes my query:
> > > When I bind a form to a table my date fields work like a charm.
> > > However when I bind a form to a view, Access spits the dummy on my 
> > > date fields saying it can´t convert nvarchar to smalldatetime bla bla.
> > > Is this normal behaviour?
> > >
> > > I have found myself wading around in date issues up to my eyeballs 
> > > that I never had to give a second thought to in my ADP´s.
> > >
> > > Furthermore I have two smaller ODBC projects where this does not 
> > > happen and for the life of me I can´t glean any difference between 
> > > those that work with views and the current one I´m working on that 
> > > doesn´t.  I´m definitely getting too old for this!
> > >
> > > Anyone out there why knows what gives?
> > >
> > > Anita from down under
> > >
> > > Anita Smith
> >