Searching multiple tables in Access using Visual Basic

[Katrina]

I am trying to write a piece of code that will search through a number
of different tables (current one being tableNm) to look for a specific
street name that has been entered by the user and saved as
Enteredstreet. If the street is found I then want to display a message
box saying what table the street name is in. The code I am currently
trying to use is this:

Sub dbOpentableX()

Dim dbsAddSampling As Database
Dim rstTable As Recordset

Set dbsAddSampling = CurrentDb
Set rstTable = dbsAddSampling.OpenRecordset(tableNm)

With rstTable
.Index = "STREETNM"

Do While Not .EOF
If !STREETNM = Enteredstreet Then
MsgBox "Street name found in" & tableNm

Exit Do

Else
.MoveNext

End If

Loop

End With

End Sub

But when I try to run this an error message comes up saying
'User-defined type not defined' and then highlights the bit of code
saying 'dbsAddSampling As Database'. I have probably done something
really stupid, or else the code is just rubbish, but I would
appreciate any help.

Katrina

[Lyle Fairfield]

Katrina wrote:

I am trying to write a piece of code that will search through a number
of different tables (current one being tableNm) to look for a specific
street name that has been entered by the user and saved as
Enteredstreet. If the street is found I then want to display a message
box saying what table the street name is in. The code I am currently
trying to use is this:

Sub dbOpentableX()

Dim dbsAddSampling As Database
Dim rstTable As Recordset

Set dbsAddSampling = CurrentDb
Set rstTable = dbsAddSampling.OpenRecordset(tableNm)

With rstTable
.Index = "STREETNM"

Do While Not .EOF
If !STREETNM = Enteredstreet Then
MsgBox "Street name found in" & tableNm

Exit Do

Else
.MoveNext

End If

Loop

End With

End Sub

But when I try to run this an error message comes up saying
'User-defined type not defined' and then highlights the bit of code
saying 'dbsAddSampling As Database'. I have probably done something
really stupid, or else the code is just rubbish, but I would
appreciate any help.

Katrina

You might try

If (CurrentProject.Connection.Execute( _
"SELECT * FROM tbl2002Transactions " _
& "WHERE fldDescription = """ & Description & """").BOF) Then
MsgBox Description & " not found"
Else
MsgBox Description & " found"
End If

substituting your own variables.

--
--
Lyle
--
From ADO28.chm

Deprecated Components
Each of the following components is considered obsolete. While these
components are still supported in this release of the Microsoft® Data
Access Components (MDAC), they may be removed in the future. When
writing new applications, you should avoid using these deprecated
components. When modifying existing applications, you are strongly
encouraged to remove any dependency on these components.

ODBC Provider (MSDASQL)
You are strongly encouraged to use one of the native OLE DB Providers
instead of the Microsoft Open Database Connectivity (ODBC) Provider.
Native OLE DB Providers provide better application stability and
performance. Furthermore, native OLE DB Providers will be supported in
the future, whereas MSDASQL will not have any new features added to it,
will not be available on 64-bit, and will not be accessible from the OLE
DB NET Data Provider.

Remote Data Services (RDS)
Remote Data Services (RDS) is a proprietary Microsoft mechanism for
accessing remote data across the Internet or intranet. Microsoft is now
shipping the Microsoft Simple Object Access Protocol (SOAP) Toolkit 2.0
that enables you to access remote data using an open, XML-based
standard. Given the availability of the SOAP Toolkit 2.0, you should
migrate from RDS to SOAP. The SOAP 2.0 Toolkit 2.0 also includes sample
code for remotely accessing Microsoft ActiveX® Data Objects (ADO)
Recordsets.

Jet and Replication Objects (JRO)
The Microsoft Jet OLE DB Provider and other related components were
removed from MDAC 2.6. Microsoft has deprecated the Microsoft Jet
Engine, and plans no new releases or service packs for this component.
As a result, the Jet and Replication Objects (JRO) is being deprecated
in this release and will not be available in any future MDAC releases.

.....

[Justin Hoffman]

"Katrina" <tr******@hotmail.com> wrote in message
news:8c**************************@posting.google.c om...

I am trying to write a piece of code that will search through a number
of different tables (current one being tableNm) to look for a specific
street name that has been entered by the user and saved as
Enteredstreet. If the street is found I then want to display a message
box saying what table the street name is in. The code I am currently
trying to use is this:

Sub dbOpentableX()

Dim dbsAddSampling As Database
Dim rstTable As Recordset

Set dbsAddSampling = CurrentDb
Set rstTable = dbsAddSampling.OpenRecordset(tableNm)

With rstTable
.Index = "STREETNM"

Do While Not .EOF
If !STREETNM = Enteredstreet Then
MsgBox "Street name found in" & tableNm

Exit Do

Else
.MoveNext

End If

Loop

End With

End Sub

But when I try to run this an error message comes up saying
'User-defined type not defined' and then highlights the bit of code
saying 'dbsAddSampling As Database'. I have probably done something
really stupid, or else the code is just rubbish, but I would
appreciate any help.

Katrina

The first problem is references. While viewing your code, select
Tools>References and make sure you have a reference set to Microsoft DAO 3.6
Object Library. Remove any references you don't need, such as ActiveX Data
Objects. You could alter the code to:
Dim dbsAddSampling As DAO.Database
Dim rstTable As DAO.Recordset
To make sure it is clear that you are using the DAO object library.

The next comment is that something must be a bit funny about the database
structure if you have to search multiple tables for the same thing.
Normally, you would expect similar information to be stored in a single
table. Perhaps that is out of your control.

The third is that it probably doesn't make sense to open an editable
recordset based on the whole table. It is a bit wasteful since you only
need to find if there is a matching entry or not. This recordset would be
better:
strSQL="SELECT STREETNM FROM MY_TABLE WHERE STREETNM=""" & strStreet & """"
Set rst=dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
However, you could use other alternatives or even scrap most of the code and
use a function like DLOOKUP, DFIRST or similar.

[trinab19]

Thanks Justin. What you suggested worked!!!

Katrina