Links tables to one table

[janith]

Hi

I have a db , which has link tables from other separate access files.now within my db I want these links tables to be in one table again. how can i achieve this...

 

[sparks80]

Hi,

Do the tables you wish to combine contain the same fields?

If so there would be no need to create a new table. You could use a union query to join the results from the multiple tables together.

So imagine two tables, unimaginatively named Table1 and Table2, both containing the equally unimaginative Field1 and Field2.

You could combine the results from both tables together using the following query:

SELECT Table1.Field1, Table1.Field2 FROM Table1 UNION ALL SELECT Table2.Field1, Table2.Field2 FROM Table2

Hopefully there should be no need to create additional tables if I have understood correctly.

 

[janith]

how can i achieve this when there are 16 link tables?(yes.. all have same field names). where will i input this code/query. b'coz after doing that i want to analysis the entire table in excel....!!!!

 

[sparks80]

Hi,

If there are 16 tables then you can create a union query that combines them all together, e.g. SELECT ... FROM table1 UNION ALL SELECT ... FROM table2 UNION ALL SELECT ... FROM table3 UNION ALL SELECT ... FROM table 4 and so on.

Alternatively you could write some VBA code that would export the results from all 16 tables to the same worksheet in an Excel file.

Is there a specific reason that you need to export the results to Excel?

 

[janith]

Hi,

I want to do it for analysizing the data and to come up with a pivot table. for Management Information purpose... or is there a way where without combining those 16 link tables I can put all of them on excel on one go.....????

 

[sparks80]

If it is a pivot table you are after then it is likely you could achieve the results you are after using a totals and/or crosstab query.

Can you explain what you aim to achieve with the pivot table, and I will try my best to let you know if this can be handled by access.

It is certainly possible to export the data to Excel in one go, but I believe the cliche goes "why use a sledgehammer to crack a nut!

 

[janith]

Hi,

The problem is only the top management has access to MS Access, the subordinates here has only access to excel that to as "excel viewer" and viewing there own stat. is important for them that the reason . Hope this explains the necessity....

 

[sparks80]

I understand.

Ok a couple more questions:

1: Do the tables have differing names, or are they named with a pattern, e.g. TableA, TableB or Table1, Table2?

2: Do you have the same number of fields in each table, and do the fields have the same name and data type?

 

[janith]

hi,

the table names are the names of the 16 advisors

All have same field names and similar data type as well.

 

[sparks80]

OK, I'll write some sample code to copy the data from multiple tables in Access to a single worksheet in Excel.

Might take a while!

 

[sparks80]

Hi,

Ok here is the code. I have tried to make the code as generic as possible. The only part you should need to change is the table names at the top of the code.

Also you will need to set a reference to the ActiveX Data Objects library file if you have not already done so. If you're not sure what to do let me know.

Sub TestExcel()
    Dim strTableNameArray(1 To 16) As String
    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim rst As ADODB.Recordset
    Dim lngRow As Long
    Dim f As ADODB.Field
    Dim intFieldNumber As Integer
    Dim I As Long
    
    strTableNameArray(1) = "TableName1"
    strTableNameArray(2) = "TableName2"
    strTableNameArray(3) = "TableName3"
    strTableNameArray(4) = "TableName4"
    strTableNameArray(5) = "TableName5"
    strTableNameArray(6) = "TableName6"
    strTableNameArray(7) = "TableName7"
    strTableNameArray(8) = "TableName8"
    strTableNameArray(9) = "TableName9"
    strTableNameArray(10) = "TableName10"
    strTableNameArray(11) = "TableName11"
    strTableNameArray(12) = "TableName12"
    strTableNameArray(13) = "TableName13"
    strTableNameArray(14) = "TableName14"
    strTableNameArray(15) = "TableName15"
    strTableNameArray(16) = "TableName16"
    
    Set xlApp = New Excel.Application
    
    ' Use this to show the excel application
    xlApp.Visible = True
    
    ' Create a new workbook and set xlSheet as the first worksheet
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Sheets(1)
    
    ' Create new recordset object
    Set rst = New ADODB.Recordset
    
    ' Copy headers to first row of excel sheet using the field names from first table
    strSQL = "SELECT * FROM " & strTableNameArray(1)
    rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
    If Not rst.EOF Then
        For intFieldNumber = 0 To rst.Fields.Count - 1
            xlSheet.cells(1, intFieldNumber + 1).Value = rst.Fields(intFieldNumber).Name
        Next
    End If
    rst.Close
    
    ' Set starting row for values
    lngRow = 2
    
    ' Loop through tables and copy contents to Excel sheet
    For I = 1 To 16
    
        ' Open the table as an ADODB recordset
        rst.Open strTableNameArray(I), CurrentProject.Connection, adOpenStatic, adLockReadOnly
        
        ' Append the values for each record into the worksheet table
        Do While Not rst.EOF
            ' loop through fields and copy values
            For intFieldNumber = 0 To rst.Fields.Count - 1
                xlSheet.cells(lngRow, intFieldNumber + 1).Value = rst.Fields(intFieldNumber).Value
            Next
            ' Increase worksheet row counter
            lngRow = lngRow + 1
            
            ' Go to next record
            rst.MoveNext
        Loop
        
        
        ' Close recordset
        rst.Close
    Next
    
    Set rst = Nothing
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

 

[janith]

Hi Sparks80,

UR a genius man... I will implement it and confirm you the outcome...soon