Solved

Access table field search

Posted on 2013-05-17
2
356 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now