Access table field search

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.
donnie91910Asked:
Who is Participating?
 
ltlbearand3Connect With a Mentor Commented:
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
 
Dale FyeCommented:
Rick Fishers Find & Replace

Best $37 I ever spent.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.