find out field names (with out access)

[RSXchin]

I know this sounds silly, but I have a database that is too large to be opened with excel and I don't have access installed, because my boss doesn't want me to use it (I don't know).

I can use sql queries, but I DON'T know the field names in the table. How can I do a query that will return ALL field names in table "HistoryTable"?

 

[xenou]

SELECT * FROM HistoryTable;

IF that's too many records try:

SELECT TOP 5 * FROM HistoryTable;

That will return records - normally with field names. If that doesn't work talk to your IT Dept.

 

[littleiitin]

I Belive you want to fetch the name of Table fields in Excel so that you can refer in your queries written in VBA Code:

Use below code: Fill the full Path of your DB with DB Name and extension

and Table Name in Code:

Sub FindFieldNames()
Dim rstRecordset    As Object
Dim adoConn         As Object
Dim strQuery        As String
Dim i                   as Long
    Set adoConn = CreateObject("AdoDB.Connection")
    Set rstRecordset = CreateObject("AdoDB.Recordset")
    adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data source=""  enTer full DBPath with DBName.mdb "";"
 
    strQuery = "Select * from TableName"
 
    rstRecordset.Open strQuery, adoConn, 3, 3
 
    For i = 0 To rstRecordset.fields.Count - 1
        rstRecordset.movefirst
        Cells(1, i + 1) = rstRecordset.fields(i).Name
    Next
End Sub

 

[RSXchin]

See, I knew we'd see eachother more. Glad we met Littleiitin!!! Thank you Xenou, I'll try that when I get back to work. I'm starting to see how access can be better than excel ( but I know sooooo much more excel vba )

 

[Norie]

How was an Access file/database created originally and is being added to if you don't have Access?

 

[RSXchin]

It is being created by an enterprise database program called "Millenium Expert"

The filetype is ".mdb"

 

[Norie]

That would be 'Millenium Expert' with it's 'ODBC compliant database with easy data export
and import'?

 

[RSXchin]

It is great if I'm looking at data that doesn't change much. But new records are being created every 5 seconds or so.

1. I can't make the kinds of queries I want, in the program itself
2. By the time I get the report created and formatted, it is too old.
3. I'd prefer to have a query that I can refresh.
4. You don't understand how horribly bad the UI is.

The database is used to record when people badge in/out. The query I ultimately want to make is, I want it to tell me who is still here.

None of the prewritten queries give me that kind of info. What I've been doing, is returning all history from today, reverse sorting, removing duplicates (that way only the latest instance survives), and removing the rows that are badge outs.


I'm really bad at explaining things.



The End

 

[littleiitin]

Use my Code to find out the Table Fields Name.

In that you can find out where In and aout time is entered.

Then use
Select Names from TableName where Intime is not null and OutTime is null

 

[RSXchin]

actually, we badge in and out of rooms. And each activity is listed in the same field.