excel spreadsheet

[jcamp]

i'm less than a novice vba programmer, so please bear with me.

i have a spreadsheet with the data format in the first row different from the data format in succeeding rows.

how do i tell access to bring in the first row in a format different than the rest ????????

 

[crobg]

What kind of format? If it is the difference between text and number, you really can't. You can store the numbers in a text field but that creates other issues. If you are talking about bold vs normal, then Access doesn't care. It is going to save just the values of your cells.

More information is needed to answer your question correctly.

 

[jcamp]

the first row has three columns that are all text.

the rest of the rows have nine columns of various types - text, data, number...

 

[crobg]

So is that two different tables?

 

[jcamp]

one table.

example:

row1: aircraft_num left_eng_num right_eng_num

row2: left_eng_hours right_eng_hours month_1
row3: left_eng_hours right_eng_hours month_2
.
.
.
.

rown: left_eng_hours right_eng_hours month_n


my table looks like

create table mytable
(
aircraft_num varchar2,
left_eng_num varchar2,
left_eng_hours number,
right_eng_num varchar2,
right_eng_hours number
month date
)

 

[PHV]

I'm afraid you have to play with OLE Automation.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[crobg]

That is a normalization issue, which can be easily handled with the right tables in Access and VBA as long as you have a way to tell the aircraft_num from the left_eng_num_hours fields.

First you have some decisions to make. You need to assign your first row of data to the rows that contain your maintenance data. So you could have an Aircraft table and a Maintenance table.

Aircraft table
Aircraft_num, Number, Primary Key (No duplicates)
left_eng_num, number
right_eng_num, number

Maintenance table
MaintenanceID, AutoNumber, Primary Key
Aircraft_num, number
Maintenance Field 1
Maintenance Field 2….

You can also just have one table but I can’t recommend that. That table might look like:
MaintenanceID, AutoNumber, Primary Key
Aircraft_num, Number
left_eng_num, number
right_eng_num, number
Maintenance Field 1
Maintenance Field 2….

With either method you can use a Report to show the data the same way it was in Excel.

Now you have your tables setup now loop through your spreadsheet to and assign the data to correct tables and fields. As PHV suggested ADO is the easiest way to transfer the data. See FAQs faq705-5873 and faq705-3859. Once you have established a connection to your Access database then loop through your Excel file table. Use an IF…Then statement to check if the current row is an Aircraft record. Don’t use the three columns that are used by both sets of data.

Sub Macro2()
'This is the two table method
'Setup an ADO connection to your Access database.  Open each table
'that you will be using.

Dim lngRow As Long
lngRow = 1

With Sheet1
    Do Until .Cells(lngRow, 1) = "" 'Make sure there are no gaps in your data
        If .Cells(lngRow, 4) = "" Then
            tblAircraft![Field1] = .Cells(lngRow, 1)
            tblAircraft![Field2] = .Cells(lngRow, 2)
            tblAircraft![Field3] = .Cells(lngRow, 3)
        Else
            tblMaintenance![Field1] = .Cells(lngRow, 1)
            tblMaintenance![Field2] = .Cells(lngRow, 2)
            tblMaintenance![Field3] = .Cells(lngRow, 3)
            tblMaintenance![Field4] = .Cells(lngRow, 4)
            tblMaintenance![Field5] = .Cells(lngRow, 5)
            tblMaintenance![Field6] = .Cells(lngRow, 6)
            tblMaintenance![Field7] = .Cells(lngRow, 7)
            tblMaintenance![Field8] = .Cells(lngRow, 8)
            tblMaintenance![Field9] = .Cells(lngRow, 9)
        End If
        lngRow = lngRow + 1
    Loop
End With

'Close your connection.

End Sub

Sub Macro2()
'This is the one table method
'Setup an ADO connection to your Access database.  Open the table
'that you will be using.

Dim lngRow As Long, lngAC1 As Long, lngAC2 As Long, lngAC3 As Long
lngRow = 1

With Sheet1
    Do Until .Cells(lngRow, 1) = "" 'Make sure there are no gaps in your data
        If .Cells(lngRow, 4) = "" Then
            lngAC1 = .Cells(lngRow, 1)
            lngAC2 = .Cells(lngRow, 2)
            lngAC3 = .Cells(lngRow, 3)
            lngRow = lngRow + 1
        Else
            Table![Field1] = lngAC1
            Table![Field2] = lngAC2
            Table![Field3] = lngAC3
            Table![Field4] = .Cells(lngRow, 1)
            Table![Field5] = .Cells(lngRow, 2)
            Table![Field6] = .Cells(lngRow, 3)
            Table![Field7] = .Cells(lngRow, 4)
            Table![Field8] = .Cells(lngRow, 5)
            Table![Field9] = .Cells(lngRow, 6)
            Table![Field10] = .Cells(lngRow, 7)
            Table![Field11] = .Cells(lngRow, 8)
            Table![Field12] = .Cells(lngRow, 9)
        End If
    Loop
End With

'Close your connection.

End Sub

Let me know if this helps.

 

[jcamp]

WOW! THANKS FOR ALL THAT.

I'LL GIVE IT A TRY !!