How to fetch Column names of a table

[deepanadhi]

Hi All,

I have a table with 60+ fields, which includes around some 30 dates also as field names.
Now my requirement is that i need to move those date field-names alone into another table or a query..?



Ex : Table

Field 1 | Field2 | FIeld3(date) | Field4 (Date) | and so on...
 
The new table should contain 
Dates (Column Name )
-----
Field3(Date)
FIeld4(Date)...

How to do this..

Thanks in Advance,
Deepan M

[recyan]

A crude way of doing it, if it is a one time operation :
1) Use a select query on the table which has date fields selecting the Date fields, something like :
SELECT
Field3(Date),
FIeld4(Date),
... ,
.....
FROM
myTable
WHERE
1 = 2

2) Export the results of this query to an excel sheet.
3) In the Excel sheet, you will get the Dates as Headings
4) Use Paste Special to Transpose the fields.
5) Import the Transposed data to your table in access.

Thanks

[alansidman]

Cross Posted at http://www.access-programmers.co.uk/...d.php?t=227338

See my response there. Please read the following.

http://www.excelguru.ca/content.php?184

[deepanadhi]

Hi Recyan,

plz check the following code.
I am getting the column names from TBL_PITDATA_DATES
and move that as entries to TBL_FORECASTDATES

The first set of code is used to delete the current contents of the table TBL_FORECASTDATES.
The second of code loops thru the table TBL_PITDATA_DATES and fetched the field names correctly (checked using the Message box), bu t i could not insert them to the table TBL_FORECASTDATES (Insert statement highlighted)

please help on how to do this insert. Thanks in advance.
Table TBL_FORECASTDATES contains four fields with first field as DATES into which the contents have to be moved.

 Private Sub Command141_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT TBL_FORECASTDATES.* FROM TBL_FORECASTDATES"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
Do Until rs.RecordCount = 0
With rs
.MoveFirst
.Delete
.MoveNext
End With
Loop

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("TBL_PITDATA_DATES")
Dim fld As DAO.Field
For Each fld In rs1.Fields
MsgBox (fld.Name)

insert into ("TBL_FORECASTDATES.DATES") by (fld.Name)

Next
Set fld = Nothing
End Sub

Hi Alansidman, Apologies for cross posting.


Thanks,
Deepan

[recyan]

Sorry, Am no good with VBA. Hold on till someone comes along.

At first glance,

strsqlInsert = "insert into ("TBL_FORECASTDATES.DATES") by (fld.Name)"
        dbs.Execute (strsqlInsert)

Thanks

Edit : Just curious : Is, what you are trying to do, not a one time operation ?

Thanks

[deepanadhi]

Hi,

Nope not a single time operation. This code assigned to a button.

Am getting Runtime Error 424 - Object Required.
In the Second line.

strsqlInsert = "insert into TBL_FORECASTDATES.DATES by (fld.Name)"
dbs.Execute (strsqlInsert)

Thanks for quick response
- Deepan

[recyan]

Like I said, Not in to VBA. But then again, till someone comes along :
What is the table name in to which you are trying to insert the values.
From what I know
INSERT INTO tablename (fieldName) VALUES (value).

Was wondering about the functional environment, in which this kind of operation, is not a one time operation & needs to be repeated again & again ?

Thanks

[deepanadhi]

Thanks Recyan...

It's still throwing error...
Yes the operation would be repeated daily.
A system generated excel file which consists dates as column headers will be uploaded everyday.

Thanks,
Deepan

[alansidman]

Did you try my suggestion at the other posting.

[recyan]

Yes the operation would be repeated daily.
A system generated excel file which consists dates as column headers will be uploaded everyday.

Now I understand.

It's still throwing error...

What is the error ?
Have you tried step debugging ?
Also after your insert sql, try this :

strsqlInsert = "insert into TBL_FORECASTDATES.DATES by (fld.Name)"
Debug.Print strsqlInsert
dbs.Execute (strsqlInsert)

This should ideally print the sql statement in the immediate window below.
Then run one of the insert sql statement in your query window & see what error it throws up.
Ideally, your sql in the immediate window should look something like below :

insert into MyTable (TheDates) VALUES (#12/31/2012#)

Edit :
Sorry Alan, was typing while you were posting.
God, all the time, I was thinking that the OP was trying the link that you had given ( I had not actually read it, since VBA goes a bit over my head).
Thanks

[deepanadhi]

Did you try my suggestion at the other posting.

Hi Alan,

Yeah checked the Link, cant understand much. Way over my knowledge. Am a newbie to both Access and Excel & this is my first project

Thanks,
Deepan

[recyan]

what happened with the current code that you were trying ?

Thanks

[deepanadhi]

Hi Recyan

It throws me this error

Runtime Error '3024'
could not find file "C:\Users\documents\TBL_forecsastdates.mdb"

This is printed in the immediate window
Insert into TBL_FORECASTDATES.DATE values (fld.Name)

Thanks,
Deepan

[deepanadhi]

The Current code..

[CODE]
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("TBL_PITDATA_DATES")
Dim fld As DAO.Field
For Each fld In rs1.Fields
MsgBox (fld.Name)
strSQL = " Insert into TBL_FORECASTDATES.DATE values (fld.Name)"
Debug.Print strSQL
db.Execute strSQL
Next
Set fld = Nothing[\CODE]

[recyan]

Let us give a try till someone comes along :

1) You have a first table with column names say :
field1, field2, 6/1/2012, 6/2/2012, 6/3/2012, field6, field7,..
from which you want to get the field names that are dates.
If above is true, what is the name of this table ?
If false, correct the above statement.

2) You now want to insert these date values in to another table.
If above is true, what is the name of this second table
and
what is the name of the column / field in this table in to which you want to enter these Date values?
If false, correct the above statement.

Thanks