Passing file path as parameter in Access Pass through query
Hello,
I've created a number of stored procedures that are called from an Access 2003 front/end via an Access pass-through One such procedure is show below and it is called by pass-through query named "ExportBillingCons" which passes the following code to the MS Sql server "Exec procAppendBillingConsData"
Create Procedure procAppendBillingConsData
AS
insert into tbl_BillingCons (BC_FILE_DATE, BC_TYPE, BC_START_DATE,
BC_END_DATE, BC_PACKAGE, BC_VIN, BC_ACCT_SAK, BC_DEALER_ID,
BC_REVENUE_SHARE)
select BC_FILE_DATE, BC_TYPE, BC_START_DATE,
BC_END_DATE, BC_PACKAGE, BC_VIN, BC_ACCT_SAK, BC_DEALER_ID,
BC_REVENUE_SHARE
from OPENROWSET('Microsoft.Jet.
I've created a number of stored procedures that are called from an Access 2003 front/end via an Access pass-through One such procedure is show below and it is called by pass-through query named "ExportBillingCons" which passes the following code to the MS Sql server "Exec procAppendBillingConsData"
Create Procedure procAppendBillingConsData
AS
insert into tbl_BillingCons (BC_FILE_DATE, BC_TYPE, BC_START_DATE,
BC_END_DATE, BC_PACKAGE, BC_VIN, BC_ACCT_SAK, BC_DEALER_ID,
BC_REVENUE_SHARE)
select BC_FILE_DATE, BC_TYPE, BC_START_DATE,
BC_END_DATE, BC_PACKAGE, BC_VIN, BC_ACCT_SAK, BC_DEALER_ID,
BC_REVENUE_SHARE
from OPENROWSET('Microsoft.Jet.
Unfortunately without using Dynamic SQL you cannot use parameters with OPENROWSET.
Let me rephrase that:
Unfortunately without using Dynamic SQL you cannot use variables for the OPENROWSET parameters.
Unfortunately without using Dynamic SQL you cannot use variables for the OPENROWSET parameters.
ASKER
Basically I was looking for a way to pass currentproject.FullName, perhaps via ADO to an Access Passthrough query
ASKER
I'm not sure about how Dynamic Sql is used. I'll see if I can find some examples
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello acPerkins
The only purpose of the front end will be to import data. Otherwise all access to the data on the server will be via a Hyperion Intelligent Client Reporting tool The front-end will employ workgroup security to permit only certain users to import data. No one else will have access to the front-end. So I think the security shortcomings of employing Dynamic Sql are addressed. The only remaining concern is perfomance. The biggest import load will be about 7000 records at a time, with most of the downloads being considerably less ( in the thousand record or less range) However, I'm thinking that the performance loss will outweigh the advantages of passing the filepath to the frontend as a parameter in the passthrough query. I need to think about this a bit more. What is your opinion?
The only purpose of the front end will be to import data. Otherwise all access to the data on the server will be via a Hyperion Intelligent Client Reporting tool The front-end will employ workgroup security to permit only certain users to import data. No one else will have access to the front-end. So I think the security shortcomings of employing Dynamic Sql are addressed. The only remaining concern is perfomance. The biggest import load will be about 7000 records at a time, with most of the downloads being considerably less ( in the thousand record or less range) However, I'm thinking that the performance loss will outweigh the advantages of passing the filepath to the frontend as a parameter in the passthrough query. I need to think about this a bit more. What is your opinion?
ASKER
I've read a little about sp_executesql which is used to mitigate the performance issues of Dynamic SQL. I'll take a look at that if the stored procedure executes to slow. However, the stored procedures I designed are very simple and only import data
>>What is your opinion?<<
You first have to determine if placing OPENROWSET in a Dynamic SQL statement actually functions correctly. Since I don't do Dynamic SQL I don't know for sure.
With 7000 rows I doubt you will see any significant difference in time between exec and sp_executesql. But if you need any results returned from the Insert statement, such as rows affected, sp_executesql may be your only choice.
You first have to determine if placing OPENROWSET in a Dynamic SQL statement actually functions correctly. Since I don't do Dynamic SQL I don't know for sure.
With 7000 rows I doubt you will see any significant difference in time between exec and sp_executesql. But if you need any results returned from the Insert statement, such as rows affected, sp_executesql may be your only choice.
ASKER
Hello acperkinks
Thank you for your help. I've decided to not use Dynamic Sql. The front end will reside on the network share and will only be accessed by one person who will import and export data. Since the file path to the server is static, unless a new network server replaces the original one, there is no need to pass the file path to the SQL server as a parameter in the stored procedure. At least now I know a bit more about using Dynamic SQL in stored procedure, and the advantages and the disadvantages in using it.
Thank you for your help. I've decided to not use Dynamic Sql. The front end will reside on the network share and will only be accessed by one person who will import and export data. Since the file path to the server is static, unless a new network server replaces the original one, there is no need to pass the file path to the SQL server as a parameter in the stored procedure. At least now I know a bit more about using Dynamic SQL in stored procedure, and the advantages and the disadvantages in using it.
Create Procedure procAppendBillingConsDate
@FileName varchar( 128 )
AS
...
from OPENROWSET( 'Microsoft.Jet.OLEDB.4.0',
Regs,
Yurich