Solved

Access table field search

Posted on 2013-05-17
2
358 Views
Last Modified: 2013-06-01
I need a query or code to search through the fields in my MSAccess database tables for a field that starts with the letters PO.
0
Comment
Question by:donnie91910
2 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39175209
Rick Fishers Find & Replace

Best $37 I ever spent.
0
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
ID: 39176488
Some of this depends on if you are looking for a column(field) name that starts with PO or data in a field that starts with PO.

Here is an example to find the column:
' Adapted from 
'http://blogs.technet.com/b/heyscriptingguy/archive/2006/06/08/how-can-i-list-all-the-tables-in-an-access-database.aspx
'http://blogs.technet.com/b/heyscriptingguy/archive/2007/07/09/how-can-i-list-all-the-fields-and-data-types-in-an-access-database.aspx

Const adSchemaTables = 20
Const adSchemaColumns = 4
Const strSearchString = "PO"

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\test.mdb"

' Get a List of all the tables
arrCriteria = Array(Empty, Empty, Empty, "Table") 

Set objRecordSet = objConnection.OpenSchema(adSchemaTables, arrCriteria)

' Loop Through the Table List
Do Until objRecordset.EOF
	' Loop Throught the field list
	strTableName = objRecordset("Table_Name")
    Set objFieldSchema = objConnection.OpenSchema(adSchemaColumns, _
        Array(Null, Null, strTableName))

    Do While Not objFieldSchema.EOF
		If left(objFieldSchema("Column_Name"), len(strSearchString)) = strSearchString Then
			wscript.Echo "Found '" & objFieldSchema("Column_Name") & "' in table : " & strTableName 
			wscript.quit
		End If
        objFieldSchema.MoveNext
    Loop
    objRecordset.MoveNext
Loop

msgbox "Done"

Open in new window


Here is an example to find the data:
' Adapted from 
'http://blogs.technet.com/b/heyscriptingguy/archive/2006/06/08/how-can-i-list-all-the-tables-in-an-access-database.aspx
'http://blogs.technet.com/b/heyscriptingguy/archive/2007/07/09/how-can-i-list-all-the-fields-and-data-types-in-an-access-database.aspx

Const adSchemaTables = 20
Const adSchemaColumns = 4
Const strSearchString = "PO"

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\test.mdb"

' Get a List of all the tables
arrCriteria = Array(Empty, Empty, Empty, "Table") 

Set objRecordSet = objConnection.OpenSchema(adSchemaTables, arrCriteria)

' Loop Through the Table List
Do Until objRecordset.EOF
	' Loop Through the field list
	strTableName = objRecordset("Table_Name")
    Set objFieldSchema = objConnection.OpenSchema(adSchemaColumns, _
        Array(Null, Null, strTableName))

    Do While Not objFieldSchema.EOF
		Set rsData = CreateObject("ADODB.Recordset")
		If strtableName <> "All" then
		rsData.Open "Select * from " & strTableName & " Where [" & objFieldSchema("Column_Name") & "] Like '" & strSearchString & "%'", objConnection

		Do While Not rsData.EOF and not rsData.BOF
			wscript.Echo "Found '" & objFieldSchema("Column_Name") & "' in table : " & strTableName 
			wscript.quit
		Loop
		end if
		objFieldSchema.MoveNext
	Loop

    objRecordset.MoveNext
Loop

msgbox "Done"

Open in new window


-Bear
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Common Records between Sub Queries 4 26
SQL Result Set to XML 4 36
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 32
Need help how to find where my error is in UFD 6 28
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question