Data type in SQL Back End (1 Viewer)

rvsebi

Registered User.
Local time
Today, 12:04
Joined
Jun 1, 2015
Messages
77
Hi, I used SSMA to migrate my access back end into SQL server and i linked tables from SQL to my access front end.
All my columns from access tables with data type date() now are datetime2.
I changed all datetime2 in date so now i have something like 2018-07-04. How can i change style of date to look like 04.07.2018 ( i saw that style is 104 - german here https://www.w3schools.com/sql/func_sqlserver_convert.asp) ?
There is any way to change that style automatically in sql server regional or something?

Thank you in advance!
 

Ranman256

Well-known member
Local time
Today, 06:04
Joined
Apr 9, 2015
Messages
4,337
I would say its a field property on the backend.

Tho you could try format() function on the Frontend,
But BE is better.
 

Minty

AWF VIP
Local time
Today, 10:04
Joined
Jul 26, 2013
Messages
10,353
Change them all to DateTime datatype and In Access , it should revert to your regional settings.

In SQL Server dates are normally displayed yyyy-mm-dd and entered as criteria in the same fashion.
 

rvsebi

Registered User.
Local time
Today, 12:04
Joined
Jun 1, 2015
Messages
77
I used already field property to change from datetime2 to date but now looks like 2018-07-04 and i want to change to 04.07.2018. If i use new query on sql server with "SELECT convert(varchar,Column_name,104) from dbo.Table_name" i see exactly what i want. Now question is how i can change data in table to look like in the "select convert ... ". Thank you!
 

rvsebi

Registered User.
Local time
Today, 12:04
Joined
Jun 1, 2015
Messages
77
Minty in access front end i cant change data type in linked table. I would like to do that but access tell me when i open table in design i cant change nothing there :( ... and more then this all columns with datatype date in sql have datatype text in access ??? Maybe access cant understand the datatype date from sql and transform this in string ( Text)
 
Last edited:

Minty

AWF VIP
Local time
Today, 10:04
Joined
Jul 26, 2013
Messages
10,353
Right take a step back.

When I said change the data type to DateTime I meant change it in SQL Server.
You can't change how it is displayed natively in SQL Server. You can change it for presentation purposes using a Convert() function, but you don't need to if you change the SQL data type to DateTime.

Access will deal with the DateTime SQL data type correctly, and should display it according to your windows / regional preferences.

Does that make sense?
 

rvsebi

Registered User.
Local time
Today, 12:04
Joined
Jun 1, 2015
Messages
77
Right take a step back.

When I said change the data type to DateTime I meant change it in SQL Server.
You can't change how it is displayed natively in SQL Server. You can change it for presentation purposes using a Convert() function, but you don't need to if you change the SQL data type to DateTime.

Access will deal with the DateTime SQL data type correctly, and should display it according to your windows / regional preferences.

Does that make sense?

Half of problem solved, ty :)
Now Access show me 04.07.2018 0:00:00. How can i get rid off this 0:00:00 ?
 

Minty

AWF VIP
Local time
Today, 10:04
Joined
Jul 26, 2013
Messages
10,353
Simply format the query / control to display short date.
All dates are actually stored as DateTimes you just don't normally see the time portion.
 

rvsebi

Registered User.
Local time
Today, 12:04
Joined
Jun 1, 2015
Messages
77
Simply format the query / control to display short date.
All dates are actually stored as DateTimes you just don't normally see the time portion.
I already have shortdate in all my controls with date datatype on all access forms. Still when i bring data to form i see 04.07.2018 0:00:00. I miss something maybe :(
 

Minty

AWF VIP
Local time
Today, 10:04
Joined
Jul 26, 2013
Messages
10,353
The easiest route might be to create a query against the table returning all fields, and format the output for any date fields in that and use that as your main table source.

There are some perceived performance improvements in using this method, depending on your environment.
 

rvsebi

Registered User.
Local time
Today, 12:04
Joined
Jun 1, 2015
Messages
77
Problem solved. I did it all again from the begining in this order
1. migrate all tables from access back end to sql server with ssma
2. edit all datatype datetime2() in datetime
3. create odbc to link tables from sql server to access front end
4. rename all linked tables from dbo_TableName in TableName
5. insert dbSeeChanges in all my recordset calls
That is all i do and program work very well.

The result: now in access all linked tables with datatype Date are declared like Date\Time automatically and ofcourse style of Date is like i wanted because of windows regionals ( 05.07.2018)
I think before didnt worked because i changed datatype from datetime2 to datetime after i linked tables from sql, wrong order.
Thank you all for your time!!
 

Minty

AWF VIP
Local time
Today, 10:04
Joined
Jul 26, 2013
Messages
10,353
Glad you resolved it.

Sometimes just deleting and relinking after updating the backend tables will sort out these issues, it's probably worth setting up a method to do this in VBA if you are likely to do a lot of development.
 

rvsebi

Registered User.
Local time
Today, 12:04
Joined
Jun 1, 2015
Messages
77
One more problem i found(for others to know).
If u gonna have update query with linked table check those tables on sql server to have primary key before u do the link, otherwise u will get an error something like "Operation must use an updateable table" If u did already the link, delete it modify table on sql and link again :)
 

Minty

AWF VIP
Local time
Today, 10:04
Joined
Jul 26, 2013
Messages
10,353
You must define a unique key with SQL linked tables, otherwise the ODBC Driver can't keep track of which records are being updated.
 

Users who are viewing this thread

Top Bottom