re-writing them in VB.Net but there have been higher priorities. We have had to make "minimal" changes to the Access code.
I have never developed in Access.
Access file: corr.accdb
In the VBA code:
Set qdf = CurrentDb.QueryDefs("create proc ##tempDimCorrSample 02_noID")
qdf.Execute
Query "create proc ##tempDimCorrSample 02_noID" has "ODBC Connect Str"
"ODBC;DRIVER=SQL Server;SERVER=xyz;DATABASE=abc;Trusted_Connection=Yes" to a SQL Server database (SS DB) and uses pass-through query
to do what it does, which is "create proc ##tempDimCorrSample". Is this stored procedure created in the DB the connection
string of the query points to? I have the impression it is.
The created stored procedure then uses a permenant view "abcdef" defined in the SQL Server database, which of course is written in SQL
Server SQL syntax.
I need to add to the Where clause of the view. I cannot add the additional Where clause code on my select in ##tempDimCorrSample
which selects from the view abcdef and a couple of tables. This is because the select in the view returns min(), max(), and sum() for some
variables. It also does a Group By. If I add additional exclusion of records which the view has already included (adding the exclusion on
the select from the view), it could change the what the values
that the aggregates should have returned and what affect what groups are returned.
Of course the simplest thing to do would be to clone the view in the SQL Server database and add my exclusions, then use that view. The
problem is that that database belongs to a different department and they are reluctanct to let us have a view in it for our work.
I have considered making all the queries use Access syntax so I would not need to use pass-through SQL. I would have to convert all the
query/proc code to Access syntax, which will take some doing. Then, even though the queries have connection strings pointing to the
SS DB, is there a way to get it to use my own definition of a query (for a view) in Access?
The queries have connection strings defined because they use tables in the SS DB. Would linking all the tables mean I would not have
to connection strings on the queries?
SUMMARY:
Will it work to redefine the queries in Access syntax, not use pass-through SQL, not put connection strings on the queries but instead
define links to the tables? Then would I be able to use my version of the view (actually defined as a query) in my Access database?
I would rather not do that work if it is not going to do what I need. Do you see a variation on my summary that work?
I have tried to provide all the information needed to describe the scenario but not too much.
Thanks in advance.
I have never developed in Access.
Access file: corr.accdb
In the VBA code:
Set qdf = CurrentDb.QueryDefs("create proc ##tempDimCorrSample 02_noID")
qdf.Execute
Query "create proc ##tempDimCorrSample 02_noID" has "ODBC Connect Str"
"ODBC;DRIVER=SQL Server;SERVER=xyz;DATABASE=abc;Trusted_Connection=Yes" to a SQL Server database (SS DB) and uses pass-through query
to do what it does, which is "create proc ##tempDimCorrSample". Is this stored procedure created in the DB the connection
string of the query points to? I have the impression it is.
The created stored procedure then uses a permenant view "abcdef" defined in the SQL Server database, which of course is written in SQL
Server SQL syntax.
I need to add to the Where clause of the view. I cannot add the additional Where clause code on my select in ##tempDimCorrSample
which selects from the view abcdef and a couple of tables. This is because the select in the view returns min(), max(), and sum() for some
variables. It also does a Group By. If I add additional exclusion of records which the view has already included (adding the exclusion on
the select from the view), it could change the what the values
that the aggregates should have returned and what affect what groups are returned.
Of course the simplest thing to do would be to clone the view in the SQL Server database and add my exclusions, then use that view. The
problem is that that database belongs to a different department and they are reluctanct to let us have a view in it for our work.
I have considered making all the queries use Access syntax so I would not need to use pass-through SQL. I would have to convert all the
query/proc code to Access syntax, which will take some doing. Then, even though the queries have connection strings pointing to the
SS DB, is there a way to get it to use my own definition of a query (for a view) in Access?
The queries have connection strings defined because they use tables in the SS DB. Would linking all the tables mean I would not have
to connection strings on the queries?
SUMMARY:
Will it work to redefine the queries in Access syntax, not use pass-through SQL, not put connection strings on the queries but instead
define links to the tables? Then would I be able to use my version of the view (actually defined as a query) in my Access database?
I would rather not do that work if it is not going to do what I need. Do you see a variation on my summary that work?
I have tried to provide all the information needed to describe the scenario but not too much.
Thanks in advance.
