Put query into new database

[Billy Rogers]

I have an ad hoc tool that i have built for our sales people. I creates a
new access database and puts the data they requested into 4 tables. Here's
the code I use to create the database and to create one of the tables

' This section Creates the database.
' It will delete the file if it already exists.

Dim wrkDefault As Workspace
Dim dbsNew As Database
Dim prpLoop As Property


' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)

' Make sure there isn't already a file with the name of
' the new database.
If Dir(AccessFilePath) <> "" Then Kill AccessFilePath

' Create a new encrypted database with the specified
' collating order.
Set dbsNew = wrkDefault.CreateDatabase(AccessFilePath, _
dbLangGeneral, dbEncrypt)

Set dbsNew = Nothing

'Demographic
Me.txtStatus.Text = "Running....." & vbCrLf & "Demographics & Volume"
Call AssocConcat

Dim qdfPassthrough As DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "Exec [GetAssocDemographics&Volume&CB] @MonthYearList='" &
MyAssocDates & "', @OrderList='" & MyAssocNums & "'"

Set qdfPassthrough = CurrentDb().QueryDefs("qryPassThru")
qdfPassthrough.Sql = strSQL

DoCmd.RunSQL "SELECT * INTO [AssocDemoVol] IN '" & AccessFilePath & "'
FROM [qryPassThru];"

Set qdfPassthrough = Nothing
Set rsCurr = Nothing


This all works perfectly fine. What i want to do now is to place a query
into the new database. I don't know how to do this.
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

http://thedataguru.blogspot.com/

 

[Allen Browne]

What i want to do now is to place a query into the new database.

OpenDatabase, and CreateQueryDef.

 

[Billy Rogers]

Thanks Allen

Here's the solution in case anyone want's to see it.

' Create View

Dim db As DAO.Database
Dim qdf As DAO.QueryDef


Set db = OpenDatabase(AccessFilePath)
Set qdf = db.CreateQueryDef("QueryName")

qdf.Sql = "SELECT fields from tablename;"

Set qdf = Nothing
db.Close
Set db = Nothing