Hi Friend..

Is it possible to get name of fields of particular table in MSAccess and 
ASP.
I am working on site which has lot of tables in database.To develope 
search engine which will search whole database I need a kind of query that 
will give me list of table .Then same result set with fields and keyword, 
I can form query dyanamically so that searching will be fast and easier.So 
how to get table name and fields name 


Waiting for your reply


D.Ganesh

Hi there,

I use this code below, to read the structure of the DB and generate an 
output file with the structure of the DB and some querys on basis of the 
structure. Is's a little program written in VBScript, but u can easily 
convert it to ASP of course. I don't think that will be a problem.

Good luck.

Stephan.

VBScript-file:

Const adUseClient = 3
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adSchemaTables
Const adSchemaColumns=4


Dim objSQL
Dim objConn
Dim objRS
Dim Item
Dim strResult: strResult=""
Dim strTable : strTable = ""
Dim strSelect: strSelect = "SELECT"
Dim strFields(100)
Dim intCount : intCount=0
Dim fso, MyFile

Sub OpenDB (ByRef Conn, data)			'Parameters: Conn = 
output, data = input.

  Dim DB, Dir, DSN, Path, ConnFile

  DB =  "\" & data & ".mdb"		         
  Path = "d:\inetpub\selectie\data"
  
  'Connectiestring naar de Database
  DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & DB 
& ";User ID=;Password=;"  
  	
  Set Conn = CreateObject("ADODB.Connection")
  Conn.Open DSN
  
End Sub

OpenDB ObjConn,"Selectie"

Set fso = CreateObject("Scripting.FileSystemObject")
Set MyFile = fso.CreateTextFile("file.txt", True)

'This will open a recordset with the database schema (Tables and Views)
Set rstList = objConn.OpenSchema(adSchemaTables)

With rstList
   Do While Not .EOF
     If Left(.Fields("TABLE_NAME"),4)=("TBL_") Then
        MyFile.WriteLine .Fields("TABLE_NAME") 	
     End If 
    .MoveNext 
   Loop 
End With

If IsObject(rstList) Then
	rstList.Close
	Set rstList = nothing
End If	

MyFile.WriteBlankLines(3)

Set rstList = objConn.OpenSchema(adSchemaColumns)
With rstList
   Do While Not .EOF
   	If Left(.Fields("TABLE_NAME"),4)=("TBL_") Then
   		If (strTable <> .Fields("TABLE_NAME")) Then
   			MyFile.WriteBlankLines(1)
   			strTable= .Fields("TABLE_NAME")
   			MyFile.WriteLine .Fields("TABLE_NAME")
   		End If
        MyFile.WriteLine vbTab &.Fields("COLUMN_NAME") 
    End If    
    .MoveNext     
   Loop 
End With

'Select statements bij tabellen
MyFile.WriteBlankLines(3)
strTable=""

With rstList
	.MoveFirst
   Do While Not .EOF
   	If Left(.Fields("TABLE_NAME"),4)=("TBL_") Then
   		If (strTable <> .Fields("TABLE_NAME")) Then
   			If (strTable <> "") Then
   				MyFile.Write " FROM " & strTable & vbcrlf
   			End If	
   			MyFile.Write strSelect    			
   			strTable= .Fields("TABLE_NAME")   		
	
   		Else
   			MyFile.Write ","
   		End If
        MyFile.Write " " & .Fields("TABLE_NAME") & "." & .Fields
("COLUMN_NAME") 
    End If    
    .MoveNext     
   Loop 
   If .EOF Then
   		MyFile.Write " FROM " & strTable & vbcrlf
   End If	
End With

'Select statements bij tabellen
MyFile.WriteBlankLines(3)
strTable=""

With rstList
	.MoveFirst
   Do While Not .EOF
   	If Left(.Fields("TABLE_NAME"),4)=("TBL_") Then
   		If (strTable <> .Fields("TABLE_NAME")) Then
   			strTable= .Fields("TABLE_NAME")   		
	
   			MyFile.Write "DELETE * FROM " & strTable & vbcrlf
   		End If	
    End If    
    .MoveNext     
   Loop 
End With

'Select statements bij tabellen
MyFile.WriteBlankLines(3)
strTable=""

With rstList
	.MoveFirst
   Do While Not .EOF
   	If Left(.Fields("TABLE_NAME"),4)=("TBL_") Then
   		If (strTable <> .Fields("TABLE_NAME")) Then
   			strTable= .Fields("TABLE_NAME")   		
	
   			MyFile.Write vbcrlf & "UPDATE " & strTable & " 
SET "
   		Else
   			MyFile.Write ","
   		End If
      MyFile.Write " " & .Fields("COLUMN_NAME") & "=[@" & .Fields
("COLUMN_NAME") & "]"
    End If    
    .MoveNext     
   Loop 
End With

'Select statements bij tabellen
MyFile.WriteBlankLines(3)
strTable=""

With rstList
	.MoveFirst
   Do While Not .EOF
   	If Left(.Fields("TABLE_NAME"),4)=("TBL_") Then
   		If (strTable <> .Fields("TABLE_NAME")) Then
   			If (strTable<>"") Then
   				MyFile.Write ") VALUES (" 
   				For intCount=0 To UBound(strFields)
   					If (strFields(intCount)<>"") Then
						  MyFile.Write "[@" & 
strFields(intCount) & "],"
   					End If	
   				Next
   				MyFile.Write ")"
   			End If
   			strTable= .Fields("TABLE_NAME")   	
 				MyFile.Write vbcrlf & "INSERT INTO " & 
strTable & " ( "
 				intCount=0
   		Else
   			MyFile.Write ","
   		End If
      MyFile.Write .Fields("COLUMN_NAME") 
      strFields(intCount) = .Fields("COLUMN_NAME") 
      intCount = intCount + 1
    End If    
    .MoveNext     
   Loop 
   If .EOF Then
 			MyFile.Write ") VALUES (" & vbcrlf
  		For intCount=0 To UBound(strFields)
   			If (strFields(intCount)<>"") Then
					MyFile.Write "[@" & strFields
(intCount) & "],"
   			End If	
   		Next
   		MyFile.Write ")"
   End If	
End With




'Remember to clean up
If IsObject(rstList) Then
	rstList.Close
	Set rstList = nothing
End If	


If IsObject(MyFile) Then
	MyFile.Close
	Set MyFile = nothing
End If	

If IsObject(objConn) Then
	Set ObjConn = nothing
End If	


> Hi Friend..

> Is it possible to get name of fields of particular table in MSAccess and 
A> SP.
I>  am working on site which has lot of tables in database.To develope 
s> earch engine which will search whole database I need a kind of query 
that 
w> ill give me list of table .Then same result set with fields and 
keyword, 
I>  can form query dyanamically so that searching will be fast and 
easier.So 
h> ow to get table name and fields name 

> 
W> aiting for your reply

> 
D> .Ganesh