Create a recordset of tablenames ending in_F

[dean.brunne]

Hi
We have a forecasting software with an ODBC connection into an Access
database. It imports a table, forecasts the timeseries then outputs
the results to a table in the same database with the same name as the
imported table but with "_F" added to the end. I want to create a
recordset of these tablenames with "_F" on the end only. How can I do
this?

Cheers,

Dean

 

[Dirk Goldgar]

In

We have a forecasting software with an ODBC connection into an Access
database. It imports a table, forecasts the timeseries then outputs
the results to a table in the same database with the same name as the
imported table but with "_F" added to the end. I want to create a
recordset of these tablenames with "_F" on the end only. How can I do
this?

Can I take it that you are running the code in the Access database, the
one where the tables have been created? You should be able to open you
recordset on a query of the MSysObjects table, with the appropriate
criteria:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT MSysObjects.Name FROM MsysObjects WHERE " & _
"(MSysObjects.Type = 1) AND " & _
"(Left$([Name],1) <> '~') AND " & _
"(Left$([Name],4) <> 'Msys') AND " & _
"(Right$([Name],2) = '_F') " & _
"ORDER BY MSysObjects.Name"

 

[dean.brunne]

In

We have a forecasting software with an ODBC connection into an Access
database. It imports a table, forecasts the timeseries then outputs
the results to a table in the same database with the same name as the
imported table but with "_F" added to the end. I want to create a
recordset of these tablenames with "_F" on the end only. How can I do
this?

Can I take it that you are running the code in the Access database, the
one where the tables have been created? You should be able to open you
recordset on a query of the MSysObjects table, with the appropriate
criteria:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
"SELECT MSysObjects.Name FROM MsysObjects WHERE " & _
"(MSysObjects.Type = 1) AND " & _
"(Left$([Name],1) <> '~') AND " & _
"(Left$([Name],4) <> 'Msys') AND " & _
"(Right$([Name],2) = '_F') " & _
"ORDER BY MSysObjects.Name"

Hi Dirk,

Thanks for the code. I should have specified that I wanted to run it
in a separate database and loop through the recordset. I have tried
the following in the separate database that gives me an error message
of no read permission on MSysObjects. I assume that this code does
not work in external code. Please advise.

Thanks Dean

Dim dbOther as DAO.Database
Dim strSQL as String
Dim rs as DAO.Recordset

Set dbOther = OpenDatabase ("c:\directory\file.mdb")

strSQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE " & _
"(MSysObjects.Type = 1) AND " & _
"(Left$([Name],1) <> '~') AND " & _
"(Left$([Name],4) <> 'Msys') AND " & _
"(Right$([Name],2) = '_F') " & _
"ORDER BY MSysObjects.Name"

Set rs = dbOther.OpenRecordset(strSQL)

With rs
Do Until .EOF

 

[Dirk Goldgar]

In

On Jun 14, 9:12 pm, "Dirk Goldgar" <[email protected]>
wrote:

Hi Dirk,

Thanks for the code. I should have specified that I wanted to run it
in a separate database and loop through the recordset. I have tried
the following in the separate database that gives me an error message
of no read permission on MSysObjects. I assume that this code does
not work in external code. Please advise.

Thanks Dean

Dim dbOther as DAO.Database
Dim strSQL as String
Dim rs as DAO.Recordset

Set dbOther = OpenDatabase ("c:\directory\file.mdb")

strSQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE " & _
"(MSysObjects.Type = 1) AND " & _
"(Left$([Name],1) <> '~') AND " & _
"(Left$([Name],4) <> 'Msys') AND " & _
"(Right$([Name],2) = '_F') " & _
"ORDER BY MSysObjects.Name"

Set rs = dbOther.OpenRecordset(strSQL)

With rs
Do Until .EOF

Is that database perhaps secured by workgroup security? I just ran this
test code and it worked fine:

Const DB1_Path As String = " ... <my path to an .mdb file> ... "

Dim DB1 As Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set DB1 = DBEngine.OpenDatabase(DB1_Path)

strSQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE " & _
"(MSysObjects.Type = 1) AND " & _
"(Left$([Name],1) <> '~') AND " & _
"(Left$([Name],4) <> 'Msys') " & _
"ORDER BY MSysObjects.Name"

Set rs = DB1.OpenRecordset(strSQL)

Do Until rs.EOF
Debug.Print rs!Name
rs.MoveNext
Loop

rs.Close

 

[dean.brunne]

In

On Jun 14, 9:12 pm, "Dirk Goldgar" <[email protected]>
wrote:

Hi Dirk,

Thanks for the code. I should have specified that I wanted to run it
in a separate database and loop through the recordset. I have tried
the following in the separate database that gives me an error message
of no read permission on MSysObjects. I assume that this code does
not work in external code. Please advise.

Thanks Dean

Dim dbOther as DAO.Database
Dim strSQL as String
Dim rs as DAO.Recordset

Set dbOther = OpenDatabase ("c:\directory\file.mdb")

strSQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE " & _
"(MSysObjects.Type = 1) AND " & _
"(Left$([Name],1) <> '~') AND " & _
"(Left$([Name],4) <> 'Msys') AND " & _
"(Right$([Name],2) = '_F') " & _
"ORDER BY MSysObjects.Name"

Set rs = dbOther.OpenRecordset(strSQL)

With rs
Do Until .EOF

Is that database perhaps secured by workgroup security? I just ran this
test code and it worked fine:

Const DB1_Path As String = " ... <my path to an .mdb file> ... "

Dim DB1 As Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set DB1 = DBEngine.OpenDatabase(DB1_Path)

strSQL = "SELECT MSysObjects.Name FROM MsysObjects WHERE " & _
"(MSysObjects.Type = 1) AND " & _
"(Left$([Name],1) <> '~') AND " & _
"(Left$([Name],4) <> 'Msys') " & _
"ORDER BY MSysObjects.Name"

Set rs = DB1.OpenRecordset(strSQL)

Do Until rs.EOF
Debug.Print rs!Name
rs.MoveNext
Loop

rs.Close

Hi Dirk,

This worked brilliantly. Thanks very much for your help!

Dean