Field count in another database

[Phil Stanton]

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_DblC lick(Cancel As Integer)

Dim MyDb As Database
Dim MyQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set MyDb = CurrentDb

Set MyQueryDef = MyDb.QueryDefs( "MyQueryNam e")
For i = 0 To MyQueryDef.Fiel ds.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_DblC lick(Cancel As Integer)

Dim AccApp As Access.Applicat ion
Dim OtherDb As Database, MyDb As Database
Dim OtherQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set AccApp = New Access.Applicat ion
Set OtherDb = AccApp.DBEngine .OpenDatabase(D atabaseName, False, False,
Nz(PWD))

Set MyDb = CurrentDb

Set OtherQueryDef = OtherDb.QueryDe fs("MyQueryName ")
For i = 0 To OtherQueryDef.F ields.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

[Allen Browne]

Replace your 4th line with:
Set MyDb = OpenDatabase("C :\MyFolder\MyFi le.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**@stantonfa mily.co.ukwrote in message
news:45******** **************@ ptn-nntp-reader02.plus.n et...

>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_DblC lick(Cancel As Integer)

Dim MyDb As Database
Dim MyQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set MyDb = CurrentDb

Set MyQueryDef = MyDb.QueryDefs( "MyQueryNam e")
For i = 0 To MyQueryDef.Fiel ds.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_DblC lick(Cancel As Integer)

Dim AccApp As Access.Applicat ion
Dim OtherDb As Database, MyDb As Database
Dim OtherQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set AccApp = New Access.Applicat ion
Set OtherDb = AccApp.DBEngine .OpenDatabase(D atabaseName, False, False,
Nz(PWD))

Set MyDb = CurrentDb

Set OtherQueryDef = OtherDb.QueryDe fs("MyQueryName ")
For i = 0 To OtherQueryDef.F ields.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

[Phil Stanton]

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*********@Se eSig.Invalidwro te in message
news:45******** *************** @per-qv1-newsreader-01.iinet.net.au ...

Replace your 4th line with:
Set MyDb = OpenDatabase("C :\MyFolder\MyFi le.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**@stantonfa mily.co.ukwrote in message
news:45******** **************@ ptn-nntp-reader02.plus.n et...

>>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_DblC lick(Cancel As Integer)

Dim MyDb As Database
Dim MyQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set MyDb = CurrentDb

Set MyQueryDef = MyDb.QueryDefs( "MyQueryNam e")
For i = 0 To MyQueryDef.Fiel ds.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_DblC lick(Cancel As Integer)

Dim AccApp As Access.Applicat ion
Dim OtherDb As Database, MyDb As Database
Dim OtherQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set AccApp = New Access.Applicat ion
Set OtherDb = AccApp.DBEngine .OpenDatabase(D atabaseName, False, False,
Nz(PWD))

Set MyDb = CurrentDb

Set OtherQueryDef = OtherDb.QueryDe fs("MyQueryName ")
For i = 0 To OtherQueryDef.F ields.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

[Allen Browne]

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("Q uery1")
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(varArra y) 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**@stantonfa mily.co.ukwrote in message
news:45******** **************@ ptn-nntp-reader02.plus.n et...

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*********@Se eSig.Invalidwro te in message
news:45******** *************** @per-qv1-newsreader-01.iinet.net.au ...

>Replace your 4th line with:
Set MyDb = OpenDatabase("C :\MyFolder\MyFi le.mdb")

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

"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in message
news:45******* *************** @ptn-nntp-reader02.plus.n et...

>>>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_DblC lick(Cancel As Integer)

Dim MyDb As Database
Dim MyQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set MyDb = CurrentDb

Set MyQueryDef = MyDb.QueryDefs( "MyQueryNam e")
For i = 0 To MyQueryDef.Fiel ds.Count - 1
...
Next
Exit Sub

ShowObject_Er r:
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_DblC lick(Cancel As Integer)

Dim AccApp As Access.Applicat ion
Dim OtherDb As Database, MyDb As Database
Dim OtherQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set AccApp = New Access.Applicat ion
Set OtherDb = AccApp.DBEngine .OpenDatabase(D atabaseName, False,
False, Nz(PWD))

Set MyDb = CurrentDb

Set OtherQueryDef = OtherDb.QueryDe fs("MyQueryName ")
For i = 0 To OtherQueryDef.F ields.Count - 1
...
Next
Exit Sub

ShowObject_Er r:
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

[Lyle Fairfield]

ADO solves almost everything:

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

Private Sub test()
Debug.Print GetSelectQueryC olumnCount( _
"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.

[Phil Stanton]

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******** *************@m 58g2000cwm.goog legroups.com...

ADO solves almost everything:

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

Private Sub test()
Debug.Print GetSelectQueryC olumnCount( _
"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.

[Lyle Fairfield]

On Jan 25, 1:25 pm, "Phil Stanton" <p...@stantonfa mily.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* *************** *****@m58g2000c wm.googlegroups .com...

ADO solves almost everything:

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

Private Sub test()
Debug.Print GetSelectQueryC olumnCount( _
"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 -

[Phil Stanton]

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*********@Se eSig.Invalidwro te 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("Q uery1")
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(varArra y) 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**@stantonfa mily.co.ukwrote in message
news:45******** **************@ ptn-nntp-reader02.plus.n et...

>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*********@Se eSig.Invalidwro te in message
news:45******* *************** *@per-qv1-newsreader-01.iinet.net.au ...

>>Replace your 4th line with:
Set MyDb = OpenDatabase("C :\MyFolder\MyFi le.mdb")

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

"Phil Stanton" <ph**@stantonfa mily.co.ukwrote in message
news:45****** *************** *@ptn-nntp-reader02.plus.n et...
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_DblC lick(Cancel As Integer)

Dim MyDb As Database
Dim MyQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set MyDb = CurrentDb

Set MyQueryDef = MyDb.QueryDefs( "MyQueryNam e")
For i = 0 To MyQueryDef.Fiel ds.Count - 1
...
Next
Exit Sub

ShowObject_E rr:
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_DblC lick(Cancel As Integer)

Dim AccApp As Access.Applicat ion
Dim OtherDb As Database, MyDb As Database
Dim OtherQueryDef As QueryDef

On Error GoTo ShowObject_Err

Set AccApp = New Access.Applicat ion
Set OtherDb = AccApp.DBEngine .OpenDatabase(D atabaseName, False,
False, Nz(PWD))

Set MyDb = CurrentDb

Set OtherQueryDef = OtherDb.QueryDe fs("MyQueryName ")
For i = 0 To OtherQueryDef.F ields.Count - 1
...
Next
Exit Sub

ShowObject_E rr:
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

[Lyle Fairfield]

On Jan 25, 1:25 pm, "Phil Stanton" <p...@stantonfa mily.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.

[Larry Linson]

"Phil Stanton" <ph**@stantonfa mily.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.