E
entropic groove
I have an excel spreadsheet that establishes a link to an Access 2003
DB. I need to be able to run a pre-saved make-table query with 4
parameters that is stored in my DB, and then use excel to run a pre-
saved crosstab query off of this table and a few other queries. The
results of this crosstab will ultimately be pulled into Excel. I
tried changing the make-table into a select and modifying the cross-
tab, but because of the parameters and complexity of the cross-tab, it
won't work.
The first problem is:
I "dummed" down my make-table query to eliminate all parameters. Upon
executing the following code:
Dim cmd1 As ADODB.Command
Dim rst2 As ADODB.Recordset
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = Cnxn
cmd1.CommandText = "[query name]"
Dim ct As CommandTypeEnum
ct = adCmdStoredProc
cmd1.CommandType = ct
cmd1.Execute
A new table is created with all of the correct column headings in the
DB. However, it is an empty table with no data. I've run the make-
table out of the DB directly and it works fine. Are there any ideas
as to why this isn't populating the table that it creates?
I have also tried to run the query with parameters using this code to
replace the execute command:
Dim intCount as Integer
intCount = cmd1.Parameters.Count
Dim arrPrm()
arrPrm = Array(strTitle, strName, strStart, strEnd)
cmd1.Execute ,arrPrm
intCount is always 0 when I run this (there are 4 parameters), but
then the execute line errors out saying I did not provide the right
number of parameters. Any ideas?
DB. I need to be able to run a pre-saved make-table query with 4
parameters that is stored in my DB, and then use excel to run a pre-
saved crosstab query off of this table and a few other queries. The
results of this crosstab will ultimately be pulled into Excel. I
tried changing the make-table into a select and modifying the cross-
tab, but because of the parameters and complexity of the cross-tab, it
won't work.
The first problem is:
I "dummed" down my make-table query to eliminate all parameters. Upon
executing the following code:
Dim cmd1 As ADODB.Command
Dim rst2 As ADODB.Recordset
Set cmd1 = New ADODB.Command
Set cmd1.ActiveConnection = Cnxn
cmd1.CommandText = "[query name]"
Dim ct As CommandTypeEnum
ct = adCmdStoredProc
cmd1.CommandType = ct
cmd1.Execute
A new table is created with all of the correct column headings in the
DB. However, it is an empty table with no data. I've run the make-
table out of the DB directly and it works fine. Are there any ideas
as to why this isn't populating the table that it creates?
I have also tried to run the query with parameters using this code to
replace the execute command:
Dim intCount as Integer
intCount = cmd1.Parameters.Count
Dim arrPrm()
arrPrm = Array(strTitle, strName, strStart, strEnd)
cmd1.Execute ,arrPrm
intCount is always 0 when I run this (there are 4 parameters), but
then the execute line errors out saying I did not provide the right
number of parameters. Any ideas?