looping in table field and return the name

Topic

New Reply

I use this conn:

cn.ConnectionString = “Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=” & DATA_BASE & “;Data Source=xxxxxxx”
cn.Open

and i know the name of table of DATA_BASE similar MyTable

How to retive the name of fileds in Table MyTable????

Reply | Quote

Replies

You can open a recordset rst on MyTable, then use a loop like this:

Code:

Dim fld As ADODB.Field
Dim strName As String
For Each fld In rst.Fields
  strName = fld.Name
  ...
  ...
Next fld

Reply | Quote

You can open a recordset rst on MyTable, then use a loop like this:

Code:

Dim fld As ADODB.Field
Dim strName As String
For Each fld In rst.Fields
  strName = fld.Name
  ...
  ...
Next fld

Hi Hans…
Based the code is possible to write into a text file similar c:myfile.txt the name of fileds separated from “;”?
I want to use to make the header of CSV text file.

Reply | Quote

Yes. Use code like this:

Code:

...
Dim fld As ADODB.Field
Dim strName As String
Dim strLine As String
Dim f As Integer

' Create text file
f = FreeFile
Open "C:MyFile.txt" For Output As #f

For Each fld In rst.Fields
  ' Get name of field
  strName = fld.Name
  ' Append to strLine
  strLine = strLine & ";" & strName
Next fld

' Write strLine to file, omitting the first semicolon ;
Print #f, Mid(strLine, 2)

' Close text file
Close #f
...

Reply | Quote

Yes. Use code like this:

Code:

...
Dim fld As ADODB.Field
Dim strName As String
Dim strLine As String
Dim f As Integer

' Create text file
f = FreeFile
Open "C:MyFile.txt" For Output As #f

For Each fld In rst.Fields
  ' Get name of field
  strName = fld.Name
  ' Append to strLine
  strLine = strLine & ";" & strName
Next fld

' Write strLine to file, omitting the first semicolon ;
Print #f, Mid(strLine, 2)

' Close text file
Close #f
...

wow!

But the next prob is:
i want to write (after this header) also the value of recordset separeted from “;” how to? i not have any idea.
In effet i wat to loop into recorset and create the rest of CSV

Reply | Quote

You can export from Access to a .csv file.
You can use CopyFromRecordset to import from Access into Excel; you can save the result as a .csv file.
So why should we write code for you for something that can already be done?

Reply | Quote

You can open a recordset rst on MyTable, then use a loop like this:

Code:

Dim fld As ADODB.Field
Dim strName As String
For Each fld In rst.Fields
  strName = fld.Name
  ...
  ...
Next fld

hi… is possible during this loop to know also the property of each filed?
Similar is is numeric, double text ecc….

Reply | Quote

hi… is possible during this loop to know also the property of each filed?
Similar is is numeric, double text ecc….

Have a look at Allen Browne’s TableInfo() function

Reply | Quote

I think the method Hans describes is fastest, particularly because you have the table open already. If it doesn’t work, you can try the ADOX library: ADO Extensions for Data Definition Language and Security (ADOX). I personally find ADOX hard to use, but my database work is quite limited, so that could explain it.

Reply | Quote