P
peregenem
Any ideas how to retrieve a parameter's default value for an Access/Jet
PROCEDURE a.k.a Parameter Query (PARAMETERS declaration)?
I created the proc/Query using this SQL:
CREATE PROCEDURE Proc5 (
argdata_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col =
IIF(argdata_col IS NULL, data_col, argdata_col);
It's Jet 4.0 syntax so you need to put Access XP/2003 into ANSI-92
query mode or use ADO e.g.
CurrentProject.Connection.Execute _
"CREATE PROCEDURE Proc5 (" & _
"argdata_col VARCHAR(10) = 'Hello' " & _
") AS " & _
"SELECT key_col, data_col " & _
"FROM Test " & _
"WHERE data_col = " & _
"IIF(argdata_col IS NULL, data_col, argdata_col); "
I presumed using the respective ADOX.Procedure object's Command object
that it would pre-fill the Parameter object's Value property for me
using the default, but no luck (in the VBE Immediate Window):
Set oCat = CreateObject("ADOX.Catalog")
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Tempo\TestProcs.mdb"
Set oComm = oCat.Procedures("Proc5").Command
? oComm.Parameters("argdata_col").Value = vbEmpty
True
DAO 3.6 doesn't appear to help me either:
Set db = DBEngine.OpenDatabase("C:\Tempo\TestProcs.mdb")
? db.QueryDefs("Proc5").Parameters(0).Value = vbEmpty
True
I hope I'm not faced with parsing the SQL because my next proc/Query
looks like this:
? db.QueryDefs("Proc6").SQL
PARAMETERS
argdata_col1 Text ( 10 ) = ';:,'',:;',
argdata_col2 Text ( 10 ) = 'Yes';
SELECT key_col, data_col
FROM Test
WHERE data_col =
IIF(argdata_col1 IS NULL, argdata_col2, argdata_col1);
Any ideas? Many thanks.
PROCEDURE a.k.a Parameter Query (PARAMETERS declaration)?
I created the proc/Query using this SQL:
CREATE PROCEDURE Proc5 (
argdata_col VARCHAR(10) = 'Hello'
) AS
SELECT key_col, data_col
FROM Test
WHERE data_col =
IIF(argdata_col IS NULL, data_col, argdata_col);
It's Jet 4.0 syntax so you need to put Access XP/2003 into ANSI-92
query mode or use ADO e.g.
CurrentProject.Connection.Execute _
"CREATE PROCEDURE Proc5 (" & _
"argdata_col VARCHAR(10) = 'Hello' " & _
") AS " & _
"SELECT key_col, data_col " & _
"FROM Test " & _
"WHERE data_col = " & _
"IIF(argdata_col IS NULL, data_col, argdata_col); "
I presumed using the respective ADOX.Procedure object's Command object
that it would pre-fill the Parameter object's Value property for me
using the default, but no luck (in the VBE Immediate Window):
Set oCat = CreateObject("ADOX.Catalog")
oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Tempo\TestProcs.mdb"
Set oComm = oCat.Procedures("Proc5").Command
? oComm.Parameters("argdata_col").Value = vbEmpty
True
DAO 3.6 doesn't appear to help me either:
Set db = DBEngine.OpenDatabase("C:\Tempo\TestProcs.mdb")
? db.QueryDefs("Proc5").Parameters(0).Value = vbEmpty
True
I hope I'm not faced with parsing the SQL because my next proc/Query
looks like this:
? db.QueryDefs("Proc6").SQL
PARAMETERS
argdata_col1 Text ( 10 ) = ';:,'',:;',
argdata_col2 Text ( 10 ) = 'Yes';
SELECT key_col, data_col
FROM Test
WHERE data_col =
IIF(argdata_col1 IS NULL, argdata_col2, argdata_col1);
Any ideas? Many thanks.