SQLDateAndTimeFromServer

[tanatif]

SYSTEM

SQL server 2014
MS access 2013


HI

i wont connect by DAO to sql server database
and run this query

SELECT FORMAT ( GETDATE(), 'yyyy-MM-dd', 'ar-SA' ) AS MASARDATE;

OR

please help me to correct this

Public Sub SQLDateAndTimeFromServer()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQLMASAR As String

    strSQLMASAR = "SELECT FORMAT ( GETDATE(), 'yyyy-MM-dd', 'ar-SA' )"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQLMASAR)

End Sub

THE RESULT IS STRING
THIS RESULT yyyy-MM-dd

:banghead:

 

[boerbende]

What are your regional settings? I expect that you are not in a native English speaking country (like me) so yyyy will not mean "year" but is considered "text"

 

[tanatif]

WHAT ABOUT THIS

Option Compare Binary
Option Explicit

Public Sub SQLDateAndTimeFromServer()

Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb

'Open a snapshot-type Recordset based on an SQL statement
strSQL = "SELECT FORMAT ( GETDATE(), 'yyyy-MM-dd', 'ar-SA' );"
Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

End Sub

or

Option Compare Binary
Option Explicit

Public Sub SQLDateAndTimeFromServer()
    Dim dbs As ADODB.Connection
    Dim rsSQL As ADODB.Recordset
    Dim Ctl As Control
    Dim strSQL As String
    Set dbs = CurrentProject.Connection
    Set rsSQL = New ADODB.Recordset

'Open a snapshot-type Recordset based on an SQL statement
rsSQL.Open "SELECT FORMAT (DATE(), 'yyyy-MM-dd', 'ar-SA');", dbs, adLockOptimistic
'Set rsSQL = dbs.OpenRecordset()
Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

End Sub

:banghead: :banghead:

 

[tanatif]

What are your regional settings? I expect that you are not in a native English speaking country (like me) so yyyy will not mean "year" but is considered "text"

arabic hijri date

 

[tanatif]

i wont date from sql server

i well try all most code not working with me ??

please help me

 

[pbaldy]

You can either use a pass through query or open your ADO recordset but use a connection string that points to the server. I declare a constant that is used throughout the db, so if I need to change it I only change it in one place:

Public Const dbConnectionString As String = "DRIVER=SQL Server Native Client 10.0;SERVER=10.1.1.33;Trusted_Connection=Yes;DATABASE=DatabaseNameHere"

I have an app where I want the server date/time, and I use a pass through query. You could open your DAO recordset on that.

 

[tanatif]

You can either use a pass through query or open your ADO recordset but use a connection string that points to the server. I declare a constant that is used throughout the db, so if I need to change it I only change it in one place:

Public Const dbConnectionString As String = "DRIVER=SQL Server Native Client 10.0;SERVER=10.1.1.33;Trusted_Connection=Yes;DATABASE=DatabaseNameHere"

I have an app where I want the server date/time, and I use a pass through query. You could open your DAO recordset on that.

where i put this

step by step please

 

[pbaldy]

https://support.microsoft.com/en-us/kb/303968

 

[tanatif]

https://support.microsoft.com/en-us/kb/303968

i great that


this connection query

now i have query in my access

but how to great this result string !

 

[pbaldy]

Like I said, your DAO code:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("QueryNameHere")

MsgBox rs(0)

 

[tanatif]

Like I said, your DAO code:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("QueryNameHere")

MsgBox rs(0)

Inside query masardate field content the date or text
how to great this result string!

 

[pbaldy]

Sorry, I don't understand the question.

 

[static]

If you want to know what time it is on the server, ask the server.

Create a view and connect to that from Access.

 

[tanatif]

Sorry, I don't understand the question.

GOOD JOB

YOU CAN HELP ME PLEASE COMPLEAT WITH ME

SEE HERE

I WONT USE THE QUERY RESULT SAME MSG TXT TO STRING

I'M SORRY ABOUT MY POOR ENGLISH

 

[pbaldy]

To get it from there, your simplest solution is probably:

=DLookup("FieldName", "QueryName")

replacing the 2 names.

 

[tanatif]

To get it from there, your simplest solution is probably:

=DLookup("FieldName", "QueryName")

replacing the 2 names.

THANK YOU
THANK YOU
THANK YOU

THIS IS MY CODE

Public Function DateAndTimeFromServer() As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim Mssdate As String
Set db = CurrentDb
strSQL = DLookup("MASARDATE", "MASARDATE3")
Mssdate = strSQL
DateAndTimeFromServer = Mssdate
End Function

AND ITS WORKING VERY GOOD

 

[pbaldy]

Happy to help! Your code could be reduced to:

Public Function DateAndTimeFromServer() As String
  DateAndTimeFromServer = DLookup("MASARDATE", "MASARDATE3")
End Function

 

[tanatif]

happy to help! Your code could be reduced to:

public function dateandtimefromserver() as string
  dateandtimefromserver = dlookup("masardate", "masardate3")
end function

yes this is short and best for me
thank you again :d