[AccessD] Table vs View Recordsource

[Anita Smith]

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




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 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-driver-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-driver
> -for-s
> ql-server?view=sql-server-ver15
> <https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-drive
> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com