Solved Access VBA code to retrieve server date

[Pop_Access]

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

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

 

[Josef P.]

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]

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]

=> 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.]

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]

@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

 

[CJ_London]

a quick google found a number of links, this is one

Retrieving date and time from remote server using NetRemoteTOD in VB.NET

This article will show how to retrieve the date and time from a remote server using the NetRemoteTOD function

www.codeproject.com

 

[Gasman]

From https://www.google.com/search?q=get...0mAEAoAEBwAEByAEI&sclient=mobile-gws-wiz-serp

Time on Server - Visual Basic (Classic)

Hi, I built a time clock program for our employees and I used the time on the computer for the time in and outs. The problem is that they are changing the time on the computer and then timing in. I knew this was possible, I just didn't think that they were smart enough to do it. Therefore, can...

www.tek-tips.com

 

[Gasman]

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.]

@Josef P. - OP has an Access BE per post #1 - Source=\\server name\accessname.accdb;

Thanks, had overlooked that code was added in #1.

I found that for NetRemoteTOD: http://vbnet.mvps.org/index.html?code/network/netremotetod.htm

But: shouldn't the clients and the servers of a network have synchronous times and only the time zone be different? I thought otherwise there are problems in AD.

 

[cheekybuddha]

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]

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]

Are you worried that the client might fiddle the date/time on their local machine?

 

[Pop_Access]

Are you worried that the client might fiddle the date/time on their local machine?

While it is difficult to completely prevent such actions, we can take steps to mitigate them.

 

[Gasman]

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?

 

[Pop_Access]

The links had code?

some of them using VB.NET, and other getting date for website using VBA

 

[CJ_London]

So far as I can see there is nothing in the vb.net code that can’t be used in vba.

 

[cheekybuddha]

Try this (based on one of Gasman's links):

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]

Just in case it applies, this is what I use.

Retrieve Greenwich Mean Time (GMT)

I recently had a need to retrieve the current date and time from the Internet to prevent the user from merely changing the system clock to c...

thedbguy.blogspot.com

 

[561414]

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:

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