By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,454 Members | 3,103 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,454 IT Pros & Developers. It's quick & easy.

Field count in another database

P: n/a
I am trying to count the fields in a queryDef in an external database.

If I run this in the actaal database I get Fields.count = 6 correctly

Private Sub ObjectName_DblClick(Cancel As Integer)

Dim MyDb As Database
Dim MyQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set MyDb = CurrentDb

Set MyQueryDef = MyDb.QueryDefs("MyQueryName")
For i = 0 To MyQueryDef.Fields.Count - 1
...
Next
Exit Sub

ShowObject_Err:
MsgBox Err.Description, vbCritical

End Sub

If I run a similar bit of code in an external database, depending on which
query I am referencing I get either the correct number of filds or 0 -
incorrect

Private Sub ObjectName_DblClick(Cancel As Integer)

Dim AccApp As Access.Application
Dim OtherDb As Database, MyDb As Database
Dim OtherQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set AccApp = New Access.Application
Set OtherDb = AccApp.DBEngine.OpenDatabase(DatabaseName, False, False,
Nz(PWD))

Set MyDb = CurrentDb

Set OtherQueryDef = OtherDb.QueryDefs("MyQueryName")
For i = 0 To OtherQueryDef.Fields.Count - 1
...
Next
Exit Sub

ShowObject_Err:
MsgBox Err.Description, vbCritical

End Sub

The difference appears to be whether any of the fields call any code ( for
example I use code to build up the names of a family from their individual
names).

Any ideas how to get round the proble. Using Access 2000

Thanks

Phil
Jan 25 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Replace your 4th line with:
Set MyDb = OpenDatabase("C:\MyFolder\MyFile.mdb")

Before the exit, close what you opened:
MyDb.Close

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:45**********************@ptn-nntp-reader02.plus.net...
>I am trying to count the fields in a queryDef in an external database.

If I run this in the actaal database I get Fields.count = 6 correctly

Private Sub ObjectName_DblClick(Cancel As Integer)

Dim MyDb As Database
Dim MyQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set MyDb = CurrentDb

Set MyQueryDef = MyDb.QueryDefs("MyQueryName")
For i = 0 To MyQueryDef.Fields.Count - 1
...
Next
Exit Sub

ShowObject_Err:
MsgBox Err.Description, vbCritical

End Sub

If I run a similar bit of code in an external database, depending on which
query I am referencing I get either the correct number of filds or 0 -
incorrect

Private Sub ObjectName_DblClick(Cancel As Integer)

Dim AccApp As Access.Application
Dim OtherDb As Database, MyDb As Database
Dim OtherQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set AccApp = New Access.Application
Set OtherDb = AccApp.DBEngine.OpenDatabase(DatabaseName, False, False,
Nz(PWD))

Set MyDb = CurrentDb

Set OtherQueryDef = OtherDb.QueryDefs("MyQueryName")
For i = 0 To OtherQueryDef.Fields.Count - 1
...
Next
Exit Sub

ShowObject_Err:
MsgBox Err.Description, vbCritical

End Sub

The difference appears to be whether any of the fields call any code ( for
example I use code to build up the names of a family from their individual
names).

Any ideas how to get round the proble. Using Access 2000
Jan 25 '07 #2

P: n/a
Thanks, Allen

The problem is with the OtherDB, not MyDb

The program works fine for ordinary queries in the OtherDB, it is only if
those queries contain calls to functions that I get no fields returned.
I have tried including a reference to the OtherDB in the reference section
of VBA, but that didn't help.

Culpa mea forgot to close the DB.

Any more thoughts?

Thanks

Phil

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45***********************@per-qv1-newsreader-01.iinet.net.au...
Replace your 4th line with:
Set MyDb = OpenDatabase("C:\MyFolder\MyFile.mdb")

Before the exit, close what you opened:
MyDb.Close

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:45**********************@ptn-nntp-reader02.plus.net...
>>I am trying to count the fields in a queryDef in an external database.

If I run this in the actaal database I get Fields.count = 6 correctly

Private Sub ObjectName_DblClick(Cancel As Integer)

Dim MyDb As Database
Dim MyQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set MyDb = CurrentDb

Set MyQueryDef = MyDb.QueryDefs("MyQueryName")
For i = 0 To MyQueryDef.Fields.Count - 1
...
Next
Exit Sub

ShowObject_Err:
MsgBox Err.Description, vbCritical

End Sub

If I run a similar bit of code in an external database, depending on
which query I am referencing I get either the correct number of filds or
0 - incorrect

Private Sub ObjectName_DblClick(Cancel As Integer)

Dim AccApp As Access.Application
Dim OtherDb As Database, MyDb As Database
Dim OtherQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set AccApp = New Access.Application
Set OtherDb = AccApp.DBEngine.OpenDatabase(DatabaseName, False, False,
Nz(PWD))

Set MyDb = CurrentDb

Set OtherQueryDef = OtherDb.QueryDefs("MyQueryName")
For i = 0 To OtherQueryDef.Fields.Count - 1
...
Next
Exit Sub

ShowObject_Err:
MsgBox Err.Description, vbCritical

End Sub

The difference appears to be whether any of the fields call any code (
for example I use code to build up the names of a family from their
individual names).

Any ideas how to get round the proble. Using Access 2000

Jan 25 '07 #3

P: n/a
Hmm. Yes, I'd forgotten it did that.

If Access can't give the count, It should be possible to parse the fields
from the SQL statement. The example below is incomplete. It just parses the
field list at the commas. You would really need to remove any literals
first, so you don't get caught with things like:
SELECT [LastName] & ", " & [FirstName] AS FullName

Anyway the basic approach would be:

Function FieldCount()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSql As String
Dim varArray As Variant
Dim lngStart As Long
Dim lngEnd As Long

Set db = OpenDatabase("C:\MyPath\MyFile.mdb")
Set qdf = db.QueryDefs("Query1")
strSql = qdf.SQL
lngStart = InStr(strSql, "SELECT ")
If lngStart 0& Then
lngStart = lngStart + 7&
lngEnd = InStr(lngStart, strSql, "FROM ")
strSql = Left$(strSql, lngEnd)
strSql = Mid$(strSql, lngStart)
varArray = Split(strSql, ",")
If IsArray(varArray) Then
FieldCount = UBound(varArray) + 1&
End If
End If

Set qdf = Nothing
db.Close
Set db = Nothing
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:45**********************@ptn-nntp-reader02.plus.net...
Thanks, Allen

The problem is with the OtherDB, not MyDb

The program works fine for ordinary queries in the OtherDB, it is only if
those queries contain calls to functions that I get no fields returned.
I have tried including a reference to the OtherDB in the reference section
of VBA, but that didn't help.

Culpa mea forgot to close the DB.

Any more thoughts?

Thanks

Phil

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45***********************@per-qv1-newsreader-01.iinet.net.au...
>Replace your 4th line with:
Set MyDb = OpenDatabase("C:\MyFolder\MyFile.mdb")

Before the exit, close what you opened:
MyDb.Close

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:45**********************@ptn-nntp-reader02.plus.net...
>>>I am trying to count the fields in a queryDef in an external database.

If I run this in the actaal database I get Fields.count = 6 correctly

Private Sub ObjectName_DblClick(Cancel As Integer)

Dim MyDb As Database
Dim MyQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set MyDb = CurrentDb

Set MyQueryDef = MyDb.QueryDefs("MyQueryName")
For i = 0 To MyQueryDef.Fields.Count - 1
...
Next
Exit Sub

ShowObject_Err:
MsgBox Err.Description, vbCritical

End Sub

If I run a similar bit of code in an external database, depending on
which query I am referencing I get either the correct number of filds or
0 - incorrect

Private Sub ObjectName_DblClick(Cancel As Integer)

Dim AccApp As Access.Application
Dim OtherDb As Database, MyDb As Database
Dim OtherQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set AccApp = New Access.Application
Set OtherDb = AccApp.DBEngine.OpenDatabase(DatabaseName, False,
False, Nz(PWD))

Set MyDb = CurrentDb

Set OtherQueryDef = OtherDb.QueryDefs("MyQueryName")
For i = 0 To OtherQueryDef.Fields.Count - 1
...
Next
Exit Sub

ShowObject_Err:
MsgBox Err.Description, vbCritical

End Sub

The difference appears to be whether any of the fields call any code (
for example I use code to build up the names of a family from their
individual names).

Any ideas how to get round the proble. Using Access 2000
Jan 25 '07 #4

P: n/a
ADO solves almost everything:

Public Function GetSelectQueryColumnCount( _
ByVal vFullPathtoDB$, _
ByVal vSelectQueryName$ _
)
Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
.CursorLocation = adUseClient
.Provider = "Microsoft.Jet.OLEDB.4.0"
With .Properties
.Item("Data Source") = vFullPathtoDB
End With
.Open
GetSelectQueryColumnCount = .OpenSchema( _
adSchemaColumns, _
Array(Empty, Empty, vSelectQueryName)) _
.RecordCount
End With
End Function

Private Sub test()
Debug.Print GetSelectQueryColumnCount( _
"Northwind.mdb", _
"Order Details Extended" _
)
End Sub

Of course, this assumes that Northwind.mdb is unsecured. If it has a
password or whatever one will have to modify the connection paroperties
to accommodate such.

Jan 25 '07 #5

P: n/a
Thanks Lyle.

I am not familiar with ADO ( Not even sure which library reference to use),
so I will go along with Allen Brown's solution. Actually I am trying to
extract the field names to make a table in MyDb to match against Outlook
fields.

Again many thanks for your help

Phil

"Lyle Fairfield" <ly***********@aim.comwrote in message
news:11*********************@m58g2000cwm.googlegro ups.com...
ADO solves almost everything:

Public Function GetSelectQueryColumnCount( _
ByVal vFullPathtoDB$, _
ByVal vSelectQueryName$ _
)
Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
.CursorLocation = adUseClient
.Provider = "Microsoft.Jet.OLEDB.4.0"
With .Properties
.Item("Data Source") = vFullPathtoDB
End With
.Open
GetSelectQueryColumnCount = .OpenSchema( _
adSchemaColumns, _
Array(Empty, Empty, vSelectQueryName)) _
.RecordCount
End With
End Function

Private Sub test()
Debug.Print GetSelectQueryColumnCount( _
"Northwind.mdb", _
"Order Details Extended" _
)
End Sub

Of course, this assumes that Northwind.mdb is unsecured. If it has a
password or whatever one will have to modify the connection paroperties
to accommodate such.

Jan 25 '07 #6

P: n/a
On Jan 25, 1:25 pm, "Phil Stanton" <p...@stantonfamily.co.ukwrote:
Thanks Lyle.

I am not familiar with ADO ( Not even sure which library reference to use),
so I will go along with Allen Brown's solution. Actually I am trying to
extract the field names to make a table in MyDb to match against Outlook
fields.

Again many thanks for your help

Phil

"Lyle Fairfield" <lylefairfi...@aim.comwrote in messagenews:11*********************@m58g2000cwm.go oglegroups.com...
ADO solves almost everything:
Public Function GetSelectQueryColumnCount( _
ByVal vFullPathtoDB$, _
ByVal vSelectQueryName$ _
)
Dim c As ADODB.Connection
Set c = New ADODB.Connection
With c
.CursorLocation = adUseClient
.Provider = "Microsoft.Jet.OLEDB.4.0"
With .Properties
.Item("Data Source") = vFullPathtoDB
End With
.Open
GetSelectQueryColumnCount = .OpenSchema( _
adSchemaColumns, _
Array(Empty, Empty, vSelectQueryName)) _
.RecordCount
End With
End Function
Private Sub test()
Debug.Print GetSelectQueryColumnCount( _
"Northwind.mdb", _
"Order Details Extended" _
)
End Sub
Of course, this assumes that Northwind.mdb is unsecured. If it has a
password or whatever one will have to modify the connection paroperties
to accommodate such.- Hide quoted text -- Show quoted text -
Jan 25 '07 #7

P: n/a
Thanks Allen. It sort of works, but there are odd commas within the fields
eg FirstName & ", " & LastName AS FullName.
I will play around as I should be able to check that the preceeding letter
is a letter, not a symbol ie ASC(Preeceeding letter) 48 and
ASC(Preeceeding letter) < 123

I will play.

Thanks for your help

Phil

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45***********************@per-qv1-newsreader-01.iinet.net.au...
Hmm. Yes, I'd forgotten it did that.

If Access can't give the count, It should be possible to parse the fields
from the SQL statement. The example below is incomplete. It just parses
the field list at the commas. You would really need to remove any literals
first, so you don't get caught with things like:
SELECT [LastName] & ", " & [FirstName] AS FullName

Anyway the basic approach would be:

Function FieldCount()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSql As String
Dim varArray As Variant
Dim lngStart As Long
Dim lngEnd As Long

Set db = OpenDatabase("C:\MyPath\MyFile.mdb")
Set qdf = db.QueryDefs("Query1")
strSql = qdf.SQL
lngStart = InStr(strSql, "SELECT ")
If lngStart 0& Then
lngStart = lngStart + 7&
lngEnd = InStr(lngStart, strSql, "FROM ")
strSql = Left$(strSql, lngEnd)
strSql = Mid$(strSql, lngStart)
varArray = Split(strSql, ",")
If IsArray(varArray) Then
FieldCount = UBound(varArray) + 1&
End If
End If

Set qdf = Nothing
db.Close
Set db = Nothing
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:45**********************@ptn-nntp-reader02.plus.net...
>Thanks, Allen

The problem is with the OtherDB, not MyDb

The program works fine for ordinary queries in the OtherDB, it is only if
those queries contain calls to functions that I get no fields returned.
I have tried including a reference to the OtherDB in the reference
section of VBA, but that didn't help.

Culpa mea forgot to close the DB.

Any more thoughts?

Thanks

Phil

"Allen Browne" <Al*********@SeeSig.Invalidwrote in message
news:45***********************@per-qv1-newsreader-01.iinet.net.au...
>>Replace your 4th line with:
Set MyDb = OpenDatabase("C:\MyFolder\MyFile.mdb")

Before the exit, close what you opened:
MyDb.Close

"Phil Stanton" <ph**@stantonfamily.co.ukwrote in message
news:45**********************@ptn-nntp-reader02.plus.net...
I am trying to count the fields in a queryDef in an external database.

If I run this in the actaal database I get Fields.count = 6 correctly

Private Sub ObjectName_DblClick(Cancel As Integer)

Dim MyDb As Database
Dim MyQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set MyDb = CurrentDb

Set MyQueryDef = MyDb.QueryDefs("MyQueryName")
For i = 0 To MyQueryDef.Fields.Count - 1
...
Next
Exit Sub

ShowObject_Err:
MsgBox Err.Description, vbCritical

End Sub

If I run a similar bit of code in an external database, depending on
which query I am referencing I get either the correct number of filds
or 0 - incorrect

Private Sub ObjectName_DblClick(Cancel As Integer)

Dim AccApp As Access.Application
Dim OtherDb As Database, MyDb As Database
Dim OtherQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set AccApp = New Access.Application
Set OtherDb = AccApp.DBEngine.OpenDatabase(DatabaseName, False,
False, Nz(PWD))

Set MyDb = CurrentDb

Set OtherQueryDef = OtherDb.QueryDefs("MyQueryName")
For i = 0 To OtherQueryDef.Fields.Count - 1
...
Next
Exit Sub

ShowObject_Err:
MsgBox Err.Description, vbCritical

End Sub

The difference appears to be whether any of the fields call any code
( for example I use code to build up the names of a family from their
individual names).

Any ideas how to get round the proble. Using Access 2000

Jan 25 '07 #8

P: n/a
On Jan 25, 1:25 pm, "Phil Stanton" <p...@stantonfamily.co.ukwrote:
Thanks Lyle.

I am not familiar with ADO ( Not even sure which library reference to use),
so I will go along with Allen Brown's solution. Actually I am trying to
extract the field names to make a table in MyDb to match against Outlook
fields.

Again many thanks for your help

Phil
Multiple choice:

1. Lyle is almost seventy years old.
2. Lyle is the youngest regular poster in CDMA.
3. All of the above.

Jan 25 '07 #9

P: n/a
"Phil Stanton" <ph**@stantonfamily.co.ukwrote
Thanks Lyle.

I am not familiar with ADO ( Not even sure which
library reference to use),
Your Shakespeare library... "MUCH ADO ABOUT NOTHING", of course.


Jan 25 '07 #10

P: n/a


On Jan 25, 3:21 pm, "Larry Linson" <boun...@localhost.notwrote:
"Phil Stanton" <p...@stantonfamily.co.ukwrote
Thanks Lyle.
>
I am not familiar with ADO ( Not even sure which
library reference to use),

Your Shakespeare library... "MUCH ADO ABOUT NOTHING", of course.
Famous lines from that play:

Day-o, day-ay-ay-o
(Daylight come and he wan' go home)
Day, he say day, he say day, he say day, he say day, he say day-ay-ay-o
(Daylight come and he wan' go home)

[I guess the DAO was pretty slow and the Developer was up all night,
EH?]

Jan 25 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.