How to fetch Column names of a table

[deepanadhi]

Hi Recyan,

Both your statements are True.

1 . Table Name is TBL_PITDATA_DATES
2. TBL_FORECASTDATES , Column Name is "Date".

Thanks,
Deepan M

[recyan]

Have used code based on your post no 4.
Have changed the column name "Date" in TBL_FORECASTDATES to "TheDates".
Do the same at your end & try running below & see if it gives some guidelines :

Private Sub Command141_Click()
    ' The declarations
    Dim strsqlDelete As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Dim fld As DAO.Field
    Dim strsqlInsert As String
    '-----------------------------------------------------
    
    Set db = CurrentDb()
    
    '-----------------------------------------------------
    ' This empties the myDatesTable if there is any data in it
    ' Is it better to Delete the table itself & re-create a new table
    ' or
    ' Is it okay to delete all the records from the table ?
    ' Ideally, first, we need to check if myDatesTable exists
    ' If it does not exist, we need to create an empty myDatesTable
    
    strsqlDelete = "DELETE * FROM TBL_FORECASTDATES"
    db.Execute (strsqlDelete)
    
    '-----------------------------------------------------
    
    ' Select only the column / field names from the table
    ' as we do not need any data from the table
    
    strSQL = "SELECT * FROM TBL_PITDATA_DATES WHERE 1 = 2"

    ' Get the column names with no record returned
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    '-----------------------------------------------------
    
    ' Loop through the column / field names using fld.Name
    For Each fld In rs.Fields
        'MsgBox (fld.Name)
        
        ' Check if the field name is a Date
        ' ( since we want only Field Names that are Dates),
        ' if yes execute the condition,
        ' else skip
        
        If IsDate(fld.Name) Then
            'MsgBox (fld.Name)
            strsqlInsert = "INSERT INTO TBL_FORECASTDATES (TheDates) VALUES (#" & fld.Name & "#)"
            'Debug.Print strsqlInsert
        
            db.Execute (strsqlInsert)
        End If

    Next
    
    '-----------------------------------------------------
    
        
    Set fld = Nothing
    rs.Close
    Set rs = Nothing
    MsgBox ("Successful")
End Sub 

Thanks

[deepanadhi]

Thanksalot for the effort & Time Recyan.

But My bad, it still throws error in the Insert Statement.
Runtime Error 3134
Syntax Error in INSERT INTO Satement

In the Immediate window following is printed..
INSERT INTO TBL_FORECASTDATES (Date) VALUES (#05/19/2012#)

   If IsDate(fld.Name) Then
            'MsgBox (fld.Name)
            strsqlInsert = "INSERT INTO TBL_FORECASTDATES (Date) VALUES (#" & fld.Name & "#)"
            Debug.Print strsqlInsert
            db.Execute (strsqlInsert)
        End If

[recyan]

Have you tried after replacing the Field name Date with TheDates in TBL_FORECASTDATES?
Date is a reserved word. Perhaps that is causing the problem.
After changing,
retain the original
INSERT INTO TBL_FORECASTDATES (TheDates) VALUES (#" & fld.Name & "#).

Thanks

[deepanadhi]

Have you tried after replacing the Field name Date with TheDates in TBL_FORECASTDATES?
Date is a reserved word. Perhaps that is causing the problem.
After changing,
retain the original
INSERT INTO TBL_FORECASTDATES (TheDates) VALUES (#" & fld.Name & "#).

Thanks

Yup,
That was the issue all the way
The Key Word - Date.

Thanks a lot Recyan, it works great.

Thanks,
Deepan.

[recyan]

Glad you found it helpful.
As I told you. I am not in to VBA, Be sure to test it properly.

Thanks

[ssanfu]

Hi deepanadhi,

PMFJI, but you really should normalize your table structure.

I have a table with 60+ fields, which includes around some 30 dates also as field names.

You have data (the dates) as field names. Instead of one record with 60 fields (30 date fields), you should have 30 records with 31 fields (unless other field names are also "data").

From http://support.microsoft.com/kb/283878 :

First Normal Form

Eliminate repeating groups in individual tables.
Create a separate table for each set of related data.
Identify each set of related data with a primary key.

Do not use multiple fields in a single table to store similar data. For example, to track an inventory item that may come from two possible sources, an inventory record may contain fields for Vendor Code 1 and Vendor Code 2. (You have dates)

What happens when you add a third vendor? (or more dates) Adding a field is not the answer; it requires program and table modifications and does not smoothly accommodate a dynamic number of vendors. Instead, place all vendor information in a separate table called Vendors, then link inventory to vendors with an item number key, or vendors to inventory with a vendor code key.

There are a lot of sites on normalization.......