Run "Make Table" Query in Access from Excel VBA

Hi Forum,

can someone help me here?
Applications: Access 2002, Excel 2003.

I have been using the following method to get data from Access into Excel.
Set ShDest = Sheets("Tabelle2")

Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("\\STATION\PasstProReloaded\Excel.mdb")
Set MyQueryDef = MyDatabase.QueryDefs("abfPasstPro_Excel_0")

'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[CustNo]") = KdNr
End With

'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset

'clear existing data on the sheet
ShDest.Activate

Cells.Select
Selection.ClearContents
'create field headers
i = 0
With Range("A1")
For Each fld In MyRecordset.Fields
.Offset(0, i).Value = fld.Name
i = i + 1
Next fld
End With

'transfer data to Excel
Range("A2").CopyFromRecordset MyRecordset
--------------------------------------------------------

That works as it should but now I need to run a make table query from Excel and pass a parameter (KDNR) to it.
The method above does not work with this action query.
Here is the SQL from the Make Table Query in Access. How can I get Excel to run this?

strUmsatz = "SELECT dbo_STATISTIKVK.KUNDE, Sum(dbo_STATISTIKVK.WERTEK) AS WE_Gesamt, Sum(dbo_STATISTIKVK.WERTVK) AS Umsatz_Gesamt, ([Umsatz_Gesamt]-[WE_Gesamt])/[Umsatz_Gesamt] AS Spanne_PC_Gesamt, [Umsatz_Gesamt]-[WE_Gesamt] AS Spanne_EUR_Gesamt, Year([BELEGDAT]) AS Year_, dbo_STATISTIKVK.ARTIKEL INTO tbl_Excel_Umsatz " & _
"FROM dbo_STATISTIKVK " & _
"WHERE (((dbo_STATISTIKVK.MENGE)>0) AND ((dbo_STATISTIKVK.BELEGDAT)>#1/1/2010#)) " & _
"GROUP BY dbo_STATISTIKVK.KUNDE, Year([BELEGDAT]), dbo_STATISTIKVK.ARTIKEL " & _
"HAVING (((dbo_STATISTIKVK.KUNDE)=" & KDNR & ") AND ((Sum(dbo_STATISTIKVK.WERTVK))>0) AND ((dbo_STATISTIKVK.ARTIKEL)<>""VERSAND"" And (dbo_STATISTIKVK.ARTIKEL)<>""99"" And (dbo_STATISTIKVK.ARTIKEL)<>""MAN"" And (dbo_STATISTIKVK.ARTIKEL)<>""Manuell"")) " & _
"ORDER BY Year([BELEGDAT]);"

Thanks for your time and consideration

Nick

I have looked around a bit and found what I believe could be the solution but this is not working :-(
I have never used this method and really don't know where I am going wrong, the SQL works fine in Access.
Could someone please have a look at it and point me in the right direction?

Dim cmd As New ADODB.Command
Dim conn As ADODB.Connection
Dim prm As ADODB.Parameter
Dim strConn As String
Set cmd = New ADODB.Command

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "CREATE PROCEDURE procUmsatz " & _
"(CustNo long) " & _
"AS SELECT dbo_STATISTIKVK.KUNDE, Sum(dbo_STATISTIKVK.WERTEK) AS WE_Gesamt, Sum(dbo_STATISTIKVK.WERTVK) AS Umsatz_Gesamt, ([Umsatz_Gesamt]-[WE_Gesamt])/[Umsatz_Gesamt] AS Spanne_PC_Gesamt, [Umsatz_Gesamt]-[WE_Gesamt] AS Spanne_EUR_Gesamt, Year([BELEGDAT]) AS Year_, dbo_STATISTIKVK.ARTIKEL INTO tbl_Excel_Umsatz " & _
"FROM dbo_STATISTIKVK " & _
"WHERE (((dbo_STATISTIKVK.MENGE)>0) AND ((dbo_STATISTIKVK.BELEGDAT)>#1/1/2010#)) " & _
"GROUP BY dbo_STATISTIKVK.KUNDE, Year([BELEGDAT]), dbo_STATISTIKVK.ARTIKEL " & _
"HAVING (((dbo_STATISTIKVK.KUNDE)= [CustNo]) AND ((Sum(dbo_STATISTIKVK.WERTVK))>0) AND ((dbo_STATISTIKVK.ARTIKEL)<>""VERSAND"" And (dbo_STATISTIKVK.ARTIKEL)<>""99"" And (dbo_STATISTIKVK.ARTIKEL)<>""MAN"" And (dbo_STATISTIKVK.ARTIKEL)<>""Manuell"")) " & _
"ORDER BY Year([BELEGDAT])"
cmd.Execute

'--------------------------------------------------------------------------

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\STATION\PasstProReloaded\Excel.mdb"

Set conn = New ADODB.Connection
conn.Open strConn

Set cmd = New ADODB.Command
cmd.CommandText = "procUmsatz"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = conn

Set prm = cmd.CreateParameter("CustNo", adInteger, adParamInput)
cmd.Parameters.Append prm
cmd.Parameters("CustNo").Value = KdNr

'Execute the Stored Procedure
cmd.Execute

'Close the connection
conn.Close

Test Reply

I am having a problem posting a sub routine. Will send parts of the routine in
several replies.

The purpose here:
Use DAO instead of ADO
Create and Run Make Table Query
Create and Run DAO Param Query
Have all the code run from EXCEL MODULE
The code is tested and works.
It was tested in Excel 2007 against an MDB file.
The dummy table is called EmpRev and has two NUMBER
fields called EmpNo and Revenue
The MDB file is called RankValues.MDB
Create MDB and Table and add 2 records
As PROOF OF CONCEPT it would be easier to create the MDB and table just described.

End Of Part One

Please Login or Register  to view this content.

End of Part Two

Please Login or Register  to view this content.

End of Part Three

Please Login or Register  to view this content.

End of Part Four

Please Login or Register  to view this content.

End of Part Five