Solved

Access table field search

Posted on 2013-05-17
2
362 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

628 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