SQLDateAndTimeFromServer

tanatif

Registered User.
Local time
Yesterday, 16:49
Joined
Feb 10, 2016
Messages
40
SYSTEM

SQL server 2014
MS access 2013


HI

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

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

attachment.php


OR

please help me to correct this

Code:
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:
 

Attachments

  • SQ;.jpg
    SQ;.jpg
    52.8 KB · Views: 247
Last edited:
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"
 
WHAT ABOUT THIS

Code:
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

Code:
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:
 
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
 
i wont date from sql server

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

please help me
 
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:

Code:
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.
 
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:

Code:
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 :confused:

step by step please
 
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)
 
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!
 
Sorry, I don't understand the question.
 
If you want to know what time it is on the server, ask the server.

Create a view and connect to that from Access.
 
Sorry, I don't understand the question.

GOOD JOB

attachment.php



YOU CAN HELP ME PLEASE COMPLEAT WITH ME

SEE HERE

attachment.php


I WONT USE THE QUERY RESULT SAME MSG TXT TO STRING

I'M SORRY ABOUT MY POOR ENGLISH
 

Attachments

  • CpWz.jpg
    CpWz.jpg
    24.6 KB · Views: 178
  • 5565656.jpg
    5565656.jpg
    52.4 KB · Views: 183
To get it from there, your simplest solution is probably:

=DLookup("FieldName", "QueryName")

replacing the 2 names.
 
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

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 :)
 
Happy to help! Your code could be reduced to:

Code:
Public Function DateAndTimeFromServer() As String
  DateAndTimeFromServer = DLookup("MASARDATE", "MASARDATE3")
End Function
 
happy to help! Your code could be reduced to:

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

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

Users who are viewing this thread

Back
Top Bottom