[AccessD] Table vs View Recordsource

[Paul Wolstenholme]

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-driver-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
>