Solved

Access table field search

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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connection to multiple databases 13 26
Have a conversion issue with varchar to int in a SQL: Query. 1 40
awk and Pythagoras? 5 19
Where on a calculated field 1 22
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…
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 …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

830 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