#  Other Applications & Softwares  > Access Tables & Databases >  >  Run "Make Table" Query in Access from Excel VBA

## NickOn

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

----------


## NickOn

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

----------


## JohnM3

Test Reply

----------


## JohnM3

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

----------


## JohnM3

```
Please Login or Register  to view this content.
```


End of Part Two

----------


## JohnM3

```
Please Login or Register  to view this content.
```


End of Part Three

----------


## JohnM3

```
Please Login or Register  to view this content.
```


End of Part Four

----------


## JohnM3

```
Please Login or Register  to view this content.
```


End of Part Five

----------

