Change connection string/update links to SqlServer BE

[Etxezarreta]

Hi,
I need to update the connections of my VBA code, in order to rebuild the link between Access FE and SqlServerBE.
To do so I create public variables, and there values are assigned when Access opens (more precisely on the load event of the presentation form) (see section A at the bottom of the message).
My question is: what would be the correct way to get rid of "CurrentProject.AccessConnection" and to update expressions such as "Set objConnection = CurrentProject.AccessConnection" used for instance to create recordsets like in
" With objRcdset_Source_Projet
.Source = str_SQL_ProjetAffecte
.ActiveConnection = objConnection"

or to run queries like in "objConnection.Execute (str_SQL)"

Thank you!
Etxe.

***SectionA***************************************
Public str_pub_StringDeConnection As String
Public str_pub_Server As String
Public str_pub_Database As String
Public obj_pub_ConnectionSqlServer As New ADODB.Connection

Private Sub Form_Load()

str_pub_Server = "DESKTOP-5D648IQ\SQLEXPRESS"
str_pub_Database = "EssaiMigration11052020"

str_pub_StringDeConnection = "Provider=SQLNCLI11;Data Source=" & str_pub_Server & ";Initial Catalog=" & str_pub_Database & ";Integrated Security=SSPI;"

Set obj_pub_ConnectionSqlServer = New ADODB.Connection
obj_pub_ConnectionSqlServer.Open str_pub_StringDeConnection
DoCmd.OpenForm "af_aMenu", acNormal
End Sub
******************************************************************************************

 

[theDBguy]

Hi. Just curious, are you pretty much stuck to using an ADO approach? I don't know the answer to your immediate question, but I can show you how I do it using DAO, if you're interested.

 

[Etxezarreta]

Hi. Just curious, are you pretty much stuck to using an ADO approach? I don't know the answer to your immediate question, but I can show you how I do it using DAO, if you're interested.

Hello DBguy,
I need to stick to ADO indeed, as this back-end will move to Azur. This is one thing I have been told consistently, if I wanted to have good performances with a SqlServer BE and even more with a cloud shared BE (Azur), pick ADO rather than DAO.

 

[theDBguy]

Hello DBguy,
I need to stick to ADO indeed, as this back-end will move to Azur. This is one thing I have been told consistently, if I wanted to have good performances with a SqlServer BE and even more with a cloud shared BE (Azur), pick ADO rather than DAO.

Hi. Thanks for the clarification. Although I haven't heard that before, I won't argue. Perhaps the answer to your question then is to configure your own Connection object (instead of using the active one) by specifying your own ConnectionString and Provider.

Connection properties, methods, and events (ADO)

Office developer client VBA reference documentation

docs.microsoft.com

 

[Etxezarreta]

I think that my explanation made things harder to understand, I hope that my problem is not that complicated!
I will try another way: until I migrated the tables to sqlExpress, I used things like " Set objConnection = CurrentProject.AccessConnection", and it worked fine.
Now the tables are in the server "DESKTOP-5D648IQ\SQLEXPRESS ", and the database "EssaiMigration11052020", how do I have to rephrase "CurrentProject.AccessConnection", in order to be able to use Set objConnection?
Thank you.
Etxe.

 

[isladogs]

Hmm...as I'm less polite than the DB Guy, I will question that statement
What evidence do you have that using ADO to connect to a SQL Server BE is better than using DAO.
Furthermore when was that information written?

 

[Etxezarreta]

This information was written here (french forum).
You don't share that point of view obviously, I am an occasional access user and you know what you are talking about, I trust you.
What would you recommend then? What would become the VBA code using ADO recordset and connections?
Thanks.
Etxe.

 

[Minty]

I'm with Colin, we use Azure a lot, and the important thing for speed is the ODBC driver, use at least Ver 11.0 onwards, the later the better, I've never heard of a speed difference between DOA and ADO, although there are other operational differences.

 

[Good Looking Bloke]

I think that my explanation made things harder to understand, I hope that my problem is not that complicated!
I will try another way: until I migrated the tables to sqlExpress, I used things like " Set objConnection = CurrentProject.AccessConnection", and it worked fine.
Now the tables are in the server "DESKTOP-5D648IQ\SQLEXPRESS ", and the database "EssaiMigration11052020", how do I have to rephrase "CurrentProject.AccessConnection", in order to be able to use Set objConnection?
Thank you.
Etxe.

I wouldn't do it this way.

Try something along the lines of

Public gcn As New ADODB.Connection
Public Sub cssOpenConnection()

    'Open connection

    With gcn
    
        If .State = 0 Then
        
            .ConnectionString = "Provider=SQLOLEDB;Data Source=Yoursource;Database=YOurDatabase; UID=username;PWD=password;"
            
            .CursorLocation = adUseClient  ' Prevents single row being returned
            
            .Open
        
        End If
        
   End With

End Sub

To set the connection in a splash form or onload event for the database call cssOpenConnection.

I close the connection if I close my main form using a simplistic code like:

Public Sub cssCloseConnection()
    
    'gcn.Close
    
    Set gcn = Nothing
    
End Sub

As my connection is open I can use it from anywhere in my application. with ADO I use this like:

Public Sub cssUpdateField(strFieldValue As String, strTable As String, strField As String, strID As String, lngID As Long)
Dim rst As New ADODB.Recordset
Dim strSQL As String
'Example for using
'   Call cssUpdateField(CStr(Me.txtName.Value), "dbo.vw_Client", "Name", "ClientID", mlngID)

    'Builds the SQL for the records based on the passed in fields

    strSQL = "SELECT " & strField & " FROM " & strTable & " WHERE " & strID & " = " & lngID
    
    With rst
    
        .Open strSQL, gcn, adOpenKeyset, adLockPessimistic
        
            If .BOF Then
            
                .AddNew
                
            End If
        
            .Fields(strField) = strFieldValue
            
            .Update
        
        
        .Close
        
    End With
    

End Sub

Hope this is helpful

 

[Ashfaque]

Hi,

It looks fantastic idea to connect tbls while opening your db and disconnect when db closes. This ways your FE shall be with less weight.
It is more useful for me but not with ADO.

I am using DAO so can someone let me know if there is similar compatible string connection?

I tried below but while refreshing it produces Run-time error 3265 - Item not found in this collection

CurrentDb.TableDefs("T_Addresses").Connect = "Server=***.***.*.*;uid=hr;password=******;database=HRINAT"
CurrentDb.TableDefs("T_Addresses").RefreshLink

 

[theDBguy]

Hi,

It looks fantastic idea to connect tbls while opening your db and disconnect when db closes. This ways your FE shall be with less weight.
It is more useful for me but not with ADO.

I am using DAO so can someone let me know if there is similar compatible string connection?

I tried below but while refreshing it produces Run-time error 3265 - Item not found in this collection

CurrentDb.TableDefs("T_Addresses").Connect = "Server=***.***.*.*;uid=hr;password=******;database=HRINAT"
CurrentDb.TableDefs("T_Addresses").RefreshLink

Hi. Check out:

ConnectionStrings.com - Forgot that connection string? Get it here!

All connection strings in one place. Find the syntax for your database connection using ADO.NET, ADO, ODBC, OLEDB, C#, VB, VB.NET, ASP.NET and more.

www.connectionstrings.com

 

[Ashfaque]

Hi. Check out:

ConnectionStrings.com - Forgot that connection string? Get it here!

All connection strings in one place. Find the syntax for your database connection using ADO.NET, ADO, ODBC, OLEDB, C#, VB, VB.NET, ASP.NET and more.

www.connectionstrings.com

Thanks theDBguy,

I am new bee for SQL Server environment. Using first time as BE for Access FE. So it would be more supportive if you can point me specific string connection that can connect my tbls on server once the database open.

 

[theDBguy]

Thanks theDBguy,

I am new bee for SQL Server environment. Using first time as BE for Access FE. So it would be more supportive if you can point me specific string connection that can connect my tbls on server once the database open.

Did you look in this section?

SQL Server connection strings - ConnectionStrings.com

Connection strings for SQL Server. Connect using Microsoft.Data.SqlClient, SqlConnection, MSOLEDBSQL, SQLNCLI11 OLEDB, SQLNCLI10 OLEDB, SQLNCLI OLEDB.

www.connectionstrings.com