Change connection string/update links to SqlServer BE (1 Viewer)

Etxezarreta

Member
Local time
Today, 17:01
Joined
Apr 13, 2020
Messages
175
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

I’m here to help
Staff member
Local time
Today, 09:01
Joined
Oct 29, 2018
Messages
21,360
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

Member
Local time
Today, 17:01
Joined
Apr 13, 2020
Messages
175
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

I’m here to help
Staff member
Local time
Today, 09:01
Joined
Oct 29, 2018
Messages
21,360
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.
 

Etxezarreta

Member
Local time
Today, 17:01
Joined
Apr 13, 2020
Messages
175
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

MVP / VIP
Local time
Today, 16:01
Joined
Jan 14, 2017
Messages
18,186
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

Member
Local time
Today, 17:01
Joined
Apr 13, 2020
Messages
175
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

AWF VIP
Local time
Today, 16:01
Joined
Jul 26, 2013
Messages
10,355
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

Registered User.
Local time
Tomorrow, 00:01
Joined
Oct 15, 2019
Messages
30
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

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

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

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

Student
Local time
Today, 21:31
Joined
Sep 6, 2004
Messages
894
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

I’m here to help
Staff member
Local time
Today, 09:01
Joined
Oct 29, 2018
Messages
21,360
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:
 

Ashfaque

Student
Local time
Today, 21:31
Joined
Sep 6, 2004
Messages
894
Hi. Check out:
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

I’m here to help
Staff member
Local time
Today, 09:01
Joined
Oct 29, 2018
Messages
21,360
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?
 

Users who are viewing this thread

Top Bottom