CurrentDb Connection String...

new_phoenix

I would appreciate some info regarding how to connect to the currently open database using the CurrentDb connection string. I am not certain of the syntax. Here is what I have so far:

Dim dbsHeadcount As Database
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstInputFile As ADODB.Recordset
Dim cmdSQLInputFile As ADODB.Command
Dim strSQLInputFile As String
Dim rstHyperionMany As ADODB.Recordset
Dim cmdSQLHyperionMany As ADODB.Command
Dim strSQLHyperionMany As String
Dim rstHyperionOne As ADODB.Recordset
Dim cmdSQLHyperionOne As ADODB.Command
Dim strSQLHyperionOne As String
Dim strDBPath As String
Dim strFileName As String
Dim strMessage As String

Set dbsHeadcount = CurrentDb
Set cmdSQLInputFile = New ADODB.Command   
Set cmdSQLInputFile.ActiveConnection = CurrentDb.Connection   '  IS THIS RIGHT??? IF NOT WHAT IS THE SYNTAX??? :confused::confused::confused:
strSQLInputFile = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS UNIT\], " & \_
    "\[L/R/G\], \[REGION\], \[JOB FUNCTION\], \[09/12/2007 Reported\] " & \_
    "\[NUMINDEX\] FROM \[TBLINPUTFILE\]"
cmdSQLInputFile.CommandType = adCmdText
cmdSQLInputFile.CommandText = strSQLInputFile
Set rstInputFile = cmdSQLInputFile.Execute()
rstInputFile.MoveFirst

Set cmdSQLHyperionMany = New ADODB.Command
Set cmdSQLHyperionMany.ActiveConnection = CurrentDb.Connection
strSQLHyperionMany = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS UNIT\], " & \_
    "\[L/R/G\], \[REGION\], \[JOB FUNCTION\], \[09/12/2007 Reported\] " & \_
    "FROM \[tblInputFile\]"
cmdSQLHyperionMany.CommandType = adCmdText
cmdSQLHyperionMany.CommandText = strSQLHyperionMany
Set rstHyperionMany = cmdSQLHyperionMany.Execute()
rstHyperionMany.MoveFirst

The problem is how do I connect to the CurrentDb.Connection that is currently active?

Dave Kreskowiak

In what context??? In this VBA code in Access?? It looks like it, but we have to be sure. I think you might be looking for something closer to this:

Dim conn As ADODB.Connection
Dim comm As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim param As ADODB.Parameter

Set conn = Application.CurrentProject.Connection
comm.ActiveConnection = conn
...

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007

new_phoenix

Thanks, Dave!!!