Solved Access VBA code to retrieve server date (1 Viewer)

Pop_Access

Member
Local time
Yesterday, 21:02
Joined
Aug 19, 2019
Messages
66
Hi everyone,

I have an Access database with a front-end and back-end architecture. The back-end portion of the database is stored on a server. I need to write VBA code to retrieve the server date instead of my PC's date.
Can anyone please provide guidance on how to accurately retrieve the server date using VBA in an Access database with a server-based back-end?
Knowing that I tried with the following code, but still the system retrieves my PC date

Code:
Function GetServerDate() As Date
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strSQL As String
   
    ' Set up the connection string to your server-based database
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\server name\accessname.accdb;"
       
    ' Open the connection
    conn.Open
   
    ' Execute a query to retrieve the server date
   strSQL = "SELECT Now() AS ServerDate;"
    Set rs = conn.Execute(strSQL, , adCmdText + adExecuteNoRecords)
   
    ' Check if any records are returned
   If Not (rs.BOF And rs.EOF) Then
        ' Move to the first record
        rs.MoveFirst
       
        ' Get the server date value
        GetServerDate = rs("ServerDate").Value
    End If
   
    ' Close the recordset and connection
    rs.Close
    conn.Close
   
    ' Clean up the objects
    Set rs = Nothing
    Set conn = Nothing
End Function

As a non-programmer, I would greatly appreciate it if someone could provide me with the complete VBA code to accomplish this.

Thank you
 
Last edited:

Josef P.

Well-known member
Local time
Today, 06:02
Joined
Feb 2, 2023
Messages
866
server-based back-end
=> active DBMS? ... Then you can query the date via SQL and evaluate it with a recordset (DAO+PassThrough or ADDOB with OLEDB connection to the server).

But I'm probably misunderstanding you, because this seems too simple to me to be a problem. ;)
 

561414

Active member
Local time
Yesterday, 23:02
Joined
May 28, 2021
Messages
279
Just make you server send you the response of a SELECT NOW() AS MyTimeStamp
If that doesn't work, then let us know what you're using as backend.
 

Pop_Access

Member
Local time
Yesterday, 21:02
Joined
Aug 19, 2019
Messages
66
=> active DBMS? ... Then you can query the date via SQL and evaluate it with a recordset (DAO+PassThrough or ADDOB with OLEDB connection to the server).

But I'm probably misunderstanding you, because this seems too simple to me to be a problem. ;)
Thank you for your response. I apologize if my question seemed simple to you. However, as a non-programmer, I'm still learning and trying to understand the best approach to retrieve the server date in Access database using VBA.
 

Josef P.

Well-known member
Local time
Today, 06:02
Joined
Feb 2, 2023
Messages
866
Just to be on the safe side, are you using an active DBMS such as SQL Server as your backend?
Are you allowed to create views in this server database?

In principle there are 3 ways to get the date from a database server:
  1. linked view, which returns the current date in a data field.
  2. Pass through query (ODBC), which executes a select statement in the database and returns the server date
  3. ADODB query (OLEDB, with direct connection to the server), which executes the same SQL statement as the pass-through query.
For the 1st and 2nd variant you could use DLookup as for Access tables.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:02
Joined
Feb 19, 2013
Messages
16,704
@Josef P. - OP has an Access BE per post #1 - Source=\\server name\accessname.accdb;

Regret I don't know how to get the date/time per the OS of the server
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:02
Joined
Sep 21, 2011
Messages
14,528
I was thinking just run a batch file to output to text file, then read the text file?

FWIW a post that led to my link at tek-tips stated that the NetRemoteTod crashed the user systems as they were on runtime version.
 

Josef P.

Well-known member
Local time
Today, 06:02
Joined
Feb 2, 2023
Messages
866
Last edited:

cheekybuddha

AWF VIP
Local time
Today, 05:02
Joined
Jul 21, 2014
Messages
2,337
It's more worrying that you might be trying to use an Access back end over a network to a server inn a different timezone 😳
 

Pop_Access

Member
Local time
Yesterday, 21:02
Joined
Aug 19, 2019
Messages
66
It's more worrying that you might be trying to use an Access back end over a network to a server inn a different timezone 😳
The reason behind this request is to implement a mechanism that prevents end users from accessing the database beyond a demo period. By checking the server date, I aim to disable certain functionalities of the database once the demo period expires.

Since I am not a programmer, I kindly request your support in providing the complete VBA code that allows me to retrieve the server date instead of my PC's date.

so please do not worry :).
 

cheekybuddha

AWF VIP
Local time
Today, 05:02
Joined
Jul 21, 2014
Messages
2,337
Are you worried that the client might fiddle the date/time on their local machine?
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:02
Joined
Sep 21, 2011
Messages
14,528
The reason behind this request is to implement a mechanism that prevents end users from accessing the database beyond a demo period. By checking the server date, I aim to disable certain functionalities of the database once the demo period expires.

Since I am not a programmer, I kindly request your support in providing the complete VBA code that allows me to retrieve the server date instead of my PC's date.

so please do not worry :).
The links had code?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:02
Joined
Feb 19, 2013
Messages
16,704
So far as I can see there is nothing in the vb.net code that can’t be used in vba.
 

cheekybuddha

AWF VIP
Local time
Today, 05:02
Joined
Jul 21, 2014
Messages
2,337
Try this (based on one of Gasman's links):
Code:
Function GetTimeFromServer(svrName As String) As String
  GetTimeFromServer = CreateObject("WScript.Shell").Exec("Net Time \\" & svrName).StdOut.ReadAll
End Function

See the output in the Immediate Window (Ctrl+G):
?GetTimeFromServer("server name")
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:02
Joined
Oct 29, 2018
Messages
21,581
Just in case it applies, this is what I use.
 

561414

Active member
Local time
Yesterday, 23:02
Joined
May 28, 2021
Messages
279
The reason behind this request is to implement a mechanism that prevents end users from accessing the database beyond a demo period. By checking the server date, I aim to disable certain functionalities of the database once the demo period expires.
Wouldn't your users be able to open the VBA editor and change the lines of code that prevent them from using your database? If that does not matter, then maybe it would be easier to just get the time from a http request. This is a google service that returns the date in the headers when called, even if the subdomain is made up:
Code:
Sub getTime()
    Dim req As Object
    Set req = CreateObject("MSXML2.ServerXMLHTTP")
    req.Open "GET", "https://whatever.firebaseio.com/"
    req.send
    MsgBox req.getResponseHeader("Date")
End Sub
 

Users who are viewing this thread

Top Bottom