Cannot get Seek method on linked table to work.

J

j_gold

Hi,

I've been trying to figure out how to use the seek method on a linked table.
I've found a few
links here and there that reference how to do it, and I copied the one from
MS
(http://support.microsoft.com/kb/210266/) that shows a workaround. I've
copied the code used below.

I have been unsuccessful at getting it to work. I'm getting error: 3024
could not find file People;. People is a linked MySQL database that I set up
as a user DSN connection.

I am not sure what I need to do to get this to work, any and all suggestions
appreciated.

Cheers, J

Code use as supplied by MS:

Sub Seek_Attached_Table(Tablename, Indexname, SearchValue)
Dim db As Database
Dim t As TableDef
Dim rs As DAO.Recordset
Dim dbpath, SourceTable

On Error GoTo SA_Errors

Set db = DBEngine(0)(0)
dbpath = Mid(db(Tablename).connect, InStr(1, _
db(Tablename).connect, "=") + 1)
If dbpath = "" Then MsgBox "You've chosen a table already in the current
database", 64, "": Exit Sub

SourceTable = db(Tablename).SourceTableName

Set db = DBEngine(0).openDatabase(dbpath)
Set rs = db.OpenRecordset(SourceTable, DB_OPEN_TABLE)
rs.Index = Indexname
rs.Seek "=", SearchValue

If Not rs.NoMatch Then
MsgBox "Found It!", 64
Else
MsgBox "Not Found", 64
End If

rs.Close
db.Close

Exit Sub
SA_Errors:
MsgBox Error, 16, CStr(Err)
Exit Sub

End Sub



MS Code:
Sub Seek_Attached_Table(Tablename, Indexname, SearchValue)
Dim db As Database
Dim t As TableDef
Dim rs As DAO.Recordset
Dim dbpath, SourceTable

On Error GoTo SA_Errors

Set db = DBEngine(0)(0)
dbpath = Mid(db(Tablename).connect, InStr(1, _
db(Tablename).connect, "=") + 1)
If dbpath = "" Then MsgBox "You've chosen a table already in the current
database", 64, "": Exit Sub

SourceTable = db(Tablename).SourceTableName

Set db = DBEngine(0).openDatabase(dbpath)
Set rs = db.OpenRecordset(SourceTable, DB_OPEN_TABLE)
rs.Index = Indexname
rs.Seek "=", SearchValue

If Not rs.NoMatch Then
MsgBox "Found It!", 64
Else
MsgBox "Not Found", 64
End If

rs.Close
db.Close

Exit Sub
SA_Errors:
MsgBox Error, 16, CStr(Err)
Exit Sub

End Sub
 
J

Jeff Boyce

I'll hazard a guess that you are not trying to do this for its own sake, but
because it allows you/your application to do something else.

If you'll describe the underlying business need, folks here may be able to
offer a viable alternative to using Seek.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

j_gold

Hi Jeff, thanks, I was going to do that, but thought it might be too much
info ;-) And no, this is not a "just for kicks" excercise. I need to do this
for an application I'm building. New to Access programming and not having
much fun :(.

Here goes:

I have a database containing "People" and 5 tables: Person, Staff, Faculty,
Student and Researcher which are referenced by personID that I would like to
be able to search to determine which group an individual listed in Person
belongs to.

Staff must be in Person
Faculty must be in Staff
Student must be in Person
Faculty and Students "may" be a Researcher.

I have a form that presents the individuals bio detail taken from Person and
option buttons that (when I get this to work) will display which group that
individual belongs to based on the search performed on corresponding table.

I thought seek would be the fastest way to determine this, but I am open to
suggestions. If more information is required, please let me know.

Cheers,

J.
 
J

JimBurke via AccessMonster.com

It looks like you want to determine if the person represented by
'SearchValue' is in table 'Tablename', and TableName has a column that has
the name stored in 'Indexname'. If that's right, this should work:

Sub Seek_Attached_Table(Tablename, Indexname, SearchValue)

If Not IsNull(DLookup(IndexName,Tablename, IndexName & " = " & SearchValue)
) Then
MsgBox "Found It!", 64
Else
MsgBox "Not Found", 64
End If

End Sub

DLookup can return a value if you need it - the value returned is the column
specified as the first argument. I just put the name of the column
represented by 'IndexName' since I don't know anything about your tables, and
in this example you don't need a value returned, you just need to know if the
row exists. If you were interested in the value of another column then you
would put that column name as the first argument. Hope I interpreted your
intentions correctly.

It seems that what you really would want to do is, rather than display a
msgbox, simply return a value of True if the row exists, False if it doesn't.
In that case you could just make it a function:

Function Person_Is_In_Table(Tablename, Indexname, SearchValue) As Boolean

If Not IsNull(DLookup(IndexName,Tablename, IndexName & " = " & SearchValue)
) Then
Person_Is_In_Table = True
Else
Person_Is_In_Table = False
End If

End Function

Then in the code where you are trying to determine if that person is in that
table, just have something like

If Person_Is_In_Table('table','index',value) then
whatever you want to do if they're in the table...
Else
if they're not there do this...
End If


You also should add error handling to your routine!

j_gold said:
Hi Jeff, thanks, I was going to do that, but thought it might be too much
info ;-) And no, this is not a "just for kicks" excercise. I need to do this
for an application I'm building. New to Access programming and not having
much fun :(.

Here goes:

I have a database containing "People" and 5 tables: Person, Staff, Faculty,
Student and Researcher which are referenced by personID that I would like to
be able to search to determine which group an individual listed in Person
belongs to.

Staff must be in Person
Faculty must be in Staff
Student must be in Person
Faculty and Students "may" be a Researcher.

I have a form that presents the individuals bio detail taken from Person and
option buttons that (when I get this to work) will display which group that
individual belongs to based on the search performed on corresponding table.

I thought seek would be the fastest way to determine this, but I am open to
suggestions. If more information is required, please let me know.

Cheers,

J.
I'll hazard a guess that you are not trying to do this for its own sake,
but because it allows you/your application to do something else.
Click to expand...
[quoted text clipped - 101 lines]
Click to expand...
 
J

Jeff Boyce

This may not be exactly what you're looking for ...

One approach to finding out which categories a Person belongs to would be to
use a query that joins the Person table to the others, as related. You'd
use the "all records in Person and any in XXXX" join between Person and each
one of the tables that hold PersonID as a foreign key, then use the same
type of join from those to their "children" tables.

I'm also wondering if there might not be a way to use just three tables,
something like (untested):

tblPerson
PersonID
LName
FName
... other person-related facts

tlkpRole
RoleID
Role (this would be your Faculty, Staff, Student, ...)

trelPersonRole
PersonRole
PersonID
RoleID
From (date this person first assumed this role)
To (date this person left this role)

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

j_gold

Hi Jim,

Thank you for your reply. You assumed correctly, I just need to determine if
the person exists in one of the tables and a function would suit that
purposed just fine.

I'm unable to test your code at the moment, but will give it a go Tuesday.

Again, thanks for the help.

Cheers,

J

JimBurke via AccessMonster.com said:
It looks like you want to determine if the person represented by
'SearchValue' is in table 'Tablename', and TableName has a column that has
the name stored in 'Indexname'. If that's right, this should work:

Sub Seek_Attached_Table(Tablename, Indexname, SearchValue)

If Not IsNull(DLookup(IndexName,Tablename, IndexName & " = " & SearchValue)
) Then
MsgBox "Found It!", 64
Else
MsgBox "Not Found", 64
End If

End Sub

DLookup can return a value if you need it - the value returned is the column
specified as the first argument. I just put the name of the column
represented by 'IndexName' since I don't know anything about your tables, and
in this example you don't need a value returned, you just need to know if the
row exists. If you were interested in the value of another column then you
would put that column name as the first argument. Hope I interpreted your
intentions correctly.

It seems that what you really would want to do is, rather than display a
msgbox, simply return a value of True if the row exists, False if it doesn't.
In that case you could just make it a function:

Function Person_Is_In_Table(Tablename, Indexname, SearchValue) As Boolean

If Not IsNull(DLookup(IndexName,Tablename, IndexName & " = " & SearchValue)
) Then
Person_Is_In_Table = True
Else
Person_Is_In_Table = False
End If

End Function

Then in the code where you are trying to determine if that person is in that
table, just have something like

If Person_Is_In_Table('table','index',value) then
whatever you want to do if they're in the table...
Else
if they're not there do this...
End If


You also should add error handling to your routine!

j_gold said:
Hi Jeff, thanks, I was going to do that, but thought it might be too much
info ;-) And no, this is not a "just for kicks" excercise. I need to do this
for an application I'm building. New to Access programming and not having
much fun :(.

Here goes:

I have a database containing "People" and 5 tables: Person, Staff, Faculty,
Student and Researcher which are referenced by personID that I would like to
be able to search to determine which group an individual listed in Person
belongs to.

Staff must be in Person
Faculty must be in Staff
Student must be in Person
Faculty and Students "may" be a Researcher.

I have a form that presents the individuals bio detail taken from Person and
option buttons that (when I get this to work) will display which group that
individual belongs to based on the search performed on corresponding table.

I thought seek would be the fastest way to determine this, but I am open to
suggestions. If more information is required, please let me know.

Cheers,

J.
I'll hazard a guess that you are not trying to do this for its own sake,
but because it allows you/your application to do something else.
Click to expand...
[quoted text clipped - 101 lines]
End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

j_gold

Hi Jeff,

Your first thought was what I had originally tried to do, but I need to be
able to update the control on the form and when I did that I got an error
saying that I could not update controls based on expressions (not exactly the
msg, but along those lines).

Unfortunately, I cannot change the tables (they are linked to a MySQL
database) and so I will have to use as is.

Jim Burke has provided a possible solution in a previous post using DLookup
that I will try Tuesday. I'll post back and let you and Jim know if I was
successful.

Cheers,

J
 
J

j_gold

Jim and Jeff,

Sorry for the delay in confirming whether or not your methods worked. For
the record, I was able to get Jim's to work (after a couple of minor
additions)

Function person_in_table(tableName As String, indexName As String, personID
As String) As Boolean

Dim inTable As Variant

inTable = DLookup(indexName, tableName, indexName & " = " & "'" & personID
& "'")

If Not IsNull(inTable) Then
person_in_table = True
Else
person_in_table = False
End If

End Function

Again, many thanks for all your help.

Cheers,

J
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to code a button to export results of stored procedure to excel file 0
DoCmd.TransferDatabase acExport to SQL server 2005 2
Compile Error - Don't know WHY? 6
Send Query To Excel 0
Data type conversion error 0
Export specific fields from access to Excel 0
Email select page of report -- need help 0
Email Report 2

Top