CurrentProject.Connection as Variable?

[CPGDeveloper]

Hello,

So I administer and work on a db application -- Azure SQL Server Back End/MS Access Front End. I've been using ADODB Recordsets since we switched over to SQL Server, and will often write something like this:

Dim mysql as string
Dim myrs as New ADODB.Recordset
Dim myfield as string

mysql = "SELECT * FROM mytable;"
myrs.open mysql, CurrentProject.Connection
myfield = myrs.Fields(0)
myrs.close
Set myrs=Nothing

OR if the query is an action query, I will write something like:
CurrentProject.Connection.Execute mysql

My question is this syntax of 'CurrentProject.Connection' -- can it be put into some kind of global variable as it will always be the same throughout the project? or at least a variable within the subroutine?

 

[jackd]

I am not an Azure user, nor even SQL Server, but you're asking within an Access forum.
My best guess is yes. You could create a TempVar and assign to it the value of CurrentProject.Connection.

 

[Micron]

I don't understand the goal. Since the property cannot be altered on an open db, then for sure it will always be the same in that db. What's the difference between using 2 standard words vs perhaps the name of one variable? To do this in a sub routine would require declaring the variable once per procedure which is more work that simply using "Currentproject.Connection" is it not?

I try to avoid ADO wherever possible so I'm not an expert in it by any means. Seems like having to do this even once is more trouble that typing 2 words but that's just me. Perhaps there is an easier way to get the connection property into a cmd object or not even use cmd at all. Maybe if you could use such code to create a global variable, but I'm afraid I can't see how the use of a 1 word variable is so much easier than 2 to make it worthwhile.

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command
mysql = "SELECT * FROM table1;"

With cmd
   .ActiveConnection = CurrentProject.Connection
   .CommandText = mysql
End With

Set myrs = cmd.Execute

 

[CPGDeveloper]

It's just my OCD...just like I define the current db, when I don't necessarily need to:

Dim cdb as DAO.Database
Set cdb = CurrentDB
cdb.Execute mysql

But point taken -- It doesn't save me much. But I'm curious, why do you avoid ADO recordsets? My understanding is that with a SQL Back End, ADO recordsets are preferred?

 

[Micron]

My experience is limited to connected tables (linked from a back end or ODBC linked) so I've not had much use for it beyond occasionally making remote connections that are not already there. When M$ reversed course and decided that ADO development would cease in favour of DAO, it reinforced my decision to not learn ADO. Besides, can you not just link Access fe to SQL Server tables the same as you would for Access be tables?

Unless you must have temporary connections to the tables, why go to all the trouble of making dynamic connections? That's not me asking as a challenge, that's because I don't know.