Sudden break in communication between Access and SQL Express (1 Viewer)

[JohnPapa]

Suddenly and I mean suddenly (when I cam back from lunch) my access application does not communicate with SQL Server.

I use Access Office 365 and SQL Express (currently on the same pc)

When I tried to run my application I received

at the following line of code in yellow

When I try to relink tblSerial I receive

It allows me to relink and it moves on to the next opening of a table (tblIe). It allows me to relink BUT not to proceed as it did in the case of tblSerial.

Any ideas about this scary behavior?

 

[monheimx9]

Hi, I can see on the last screenshot that the database username is "savf"
By default the superadmin on SQL Express user is only "sa"

Is "savf" expected or it is a typo?

 

[Josef P.]

when I cam back from lunch

I know the problem when an ODBC data source is open and the PC goes to standby, cutting the network connection.

In your case the solution is very simple: since you use ADODB anyway, why don't you use the OLEDB connection to the server directly instead of using the ODBC linked table with CurrentProject.Connection?

 

[JohnPapa]

Hi, I can see on the last screenshot that the database username is "savf"
By default the superadmin on SQL Express user is only "sa"

Is "savf" expected or it is a typo?

savf is expected. It is the username.

 

[JohnPapa]

I know the problem when an ODBC data source is open and the PC goes to standby, cutting the network connection.

In your case the solution is very simple: since you use ADODB anyway, why don't you use the OLEDB connection to the server directly instead of using the ODBC linked table with CurrentProject.Connection?

Yes, you accurately describe the setting.

I guess I would need to do this for all table connections, which is not too many. At present I have about 20 tables.

I have never used OLEDB. Can you please provide an example?

Would it be something like

dim conn_ado as ADODB.connection
    set conn_ado = currentProject.connection

 

[JohnPapa]

BTW, is there a way to recover from the problems I am facing with ODBC?

 

[Josef P.]

ADODB Connection:
Connection strings see: https://www.connectionstrings.com/

dim conn_ado as ADODB.Connection
set conn_ado = New ADODB.Connection
conn_ado.open YourOledbConnectionString

You can leave this connection open in the background. Compared to ODBC, this does not cause any problems if the network connection is lost in the meantime.

 

[JohnPapa]

ADODB Connection:
Connection strings see: https://www.connectionstrings.com/

dim conn_ado as ADODB.Connection
set conn_ado = New ADODB.Connection
conn_ado.open YourOledbConnectionString

You can leave this connection open in the background. Compared to ODBC, this does not cause any problems if the network connection is lost in the meantime.

Many thanks, will need to do some studying and will come back.

 

[Josef P.]

Attached is a sample file, with the classes I use for data access.

This makes the use of recordsets & co. easier:

   With DbCon.ADODB.OpenRecordset("select * from dbo.tblSerial", adOpenStatic, adLockReadOnly, adUseClient, True)
      ' Use OLEDB connection to server
   End With

   With DbCon.DAO.OpenRecordset("select * from localTable")
      ' Use currentdb, ...
   End With

   With DbCon.ODBC.OpenRecordsetPT("select * from dbo.tblSerial")
      ' Use a Pass-Through-Query
   End With

 

[JohnPapa]

Attached is a sample file, with the classes I use for data access.

This makes the use of recordsets & co. easier:

   With DbCon.ADODB.OpenRecordset("select * from dbo.tblSerial", adOpenStatic, adLockReadOnly, adUseClient, True)
      ' Use OLEDB connection to server
   End With

   With DbCon.DAO.OpenRecordset("select * from localTable")
      ' Use currentdb, ...
   End With

   With DbCon.ODBC.OpenRecordsetPT("select * from dbo.tblSerial")
      ' Use a Pass-Through-Query
   End With

There is Login Form which is asking me for a username and password

Also there are a lot of parameters in form OpenMeFirst_SetDbmsParameter, which I do not know what to enter.

I am guessing the dbmsUser is in my case "savf" and dbmsPwd = "1234"

 

[Josef P.]

This should not be the case if you run the startup procedure (__ShowMe_StartModule.StartApplication) first.

I am guessing the dbmsUser is in my case "savf" and dbmsPwd = "1234"

... You only need to adjust the server and database name in this mask.

The values are stored in the table "usys_DbmsConnection" and can also be filled via add-in (DbmsConnectionWizard).

 

[JohnPapa]

Attached is a sample file, with the classes I use for data access.

This makes the use of recordsets & co. easier:

   With DbCon.ADODB.OpenRecordset("select * from dbo.tblSerial", adOpenStatic, adLockReadOnly, adUseClient, True)
      ' Use OLEDB connection to server
   End With

   With DbCon.DAO.OpenRecordset("select * from localTable")
      ' Use currentdb, ...
   End With

   With DbCon.ODBC.OpenRecordsetPT("select * from dbo.tblSerial")
      ' Use a Pass-Through-Query
   End With

On top you have ADODB, DAO and ODBC. Do I need all 3 or just the ADODB?

 

[JohnPapa]

Hi Josef P. Please let's do a reset. I am sure you know how to use OleDb, but I have not had any experience.

To get a practical example how do I create a recordset based on table for example "tblSerial", move to the first record and change tblSerial!Name to say "ABC"?

 

[Josef P.]

If you only need ADODB, you can look at the AdodbHandler class.

For example, instantiate somewhere in a standard module:

Public property Get AdoCon as AdodbHandler
     if m_AdoCon is nothing then
          set m_AdoCon = new adodbHandler
          m_AdoCon.ConnectionString = YourOledbConnectionstring
     end if
     set AdoCon = m_AdoCon
end property

...

dim rst as adodb.recordset
set rst = AdoCon.OpenRecordset(...)

The long version:

dim cnn as adodb.Connection
dim rst as adodb.recordset
dim ConnectionString as String
dim SelectSql as String

' see: https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/
'  or: https://www.connectionstrings.com/ole-db-driver-for-sql-server/
ConnectionString = "Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

set cnn = new adodb.Connection
cnn.Open ConnectionString

SelectSql = ...
set rst = new adodb.Recordset
rst.open SelectSql, cnn

....

 

[JohnPapa]

If you only need ADODB, you can look at the AdodbHandler class.

For example, instantiate somewhere in a standard module:

Public property Get AdoCon as AdodbHandler
     if m_AdoCon is nothing then
          set m_AdoCon = new adodbHandler
          m_AdoCon.ConnectionString = YourOledbConnectionstring
     end if
     set AdoCon = m_AdoCon
end property

...

dim rst as adodb.recordset
set rst = AdoCon.OpenRecordset(...)

The long version:

dim cnn as adodb.Connection
dim rst as adodb.recordset
dim ConnectionString as String
dim SelectSql as String

' see: https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/
'  or: https://www.connectionstrings.com/ole-db-driver-for-sql-server/
ConnectionString = "Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;"

set cnn = new adodb.Connection
cnn.Open ConnectionString

SelectSql = ...
set rst = new adodb.Recordset
rst.open SelectSql, cnn

....

Many thanks. Very useful. I will definitely look into it.

BTW, I solved the original problem by deleting all the links to the tables and recreating the links.

 

[Josef P.]

BTW, I solved the original problem by deleting all the links to the tables and recreating the links.

Does this work within the error handling - i.e. without having to close the application?

 

[JohnPapa]

Does this work within the error handling - i.e. without having to close the application?

Can you please clarify what you mean?

 

[Josef P.]

A scenario:
You go for lunch and leave the application open with an open table (Recordset,...).
The laptop goes into standby mode and cuts the (W)LAN connection.
You come back and want to refresh the data => error message (e.g. like in #1).

If you now react to this error and delete all linked tables and reconnect, can you continue working normally afterwards or do you have to restart the application before?

 

[JohnPapa]

Did not test exactly the scenario you mention. I only today fixed it. Next time I will test to see whether I can resume without exiting the application.

I do not know if it is related, but after the error message (do not remember exactly at which point) I was informed that I had to change the password of the SQL Express.

 

[GPGeorge]

Did not test exactly the scenario you mention. I only today fixed it. Next time I will test to see whether I can resume without exiting the application.

I do not know if it is related, but after the error message (do not remember exactly at which point) I was informed that I had to change the password of the SQL Express.

Change a password? Where did this message come from? More specific please.