Splitting Excel sheet into multiple Access Tables ?

[klong]

Hi,

I have a Spreadsheet I use for my suppliers quotes. I think it's time to make it into an Access Database and split out over a number of tables as it is growing in size.

In theory I could do it all on one table but this defeats the purpose, and will eventually hit a limit !

The purpose of the database will be to take in the various quotes, and requotes, from various suppliers and potential new suppliers. One table will be my master list (Part, Description, current price, current supplier etc) that I can compare the quotes against and track what happens after that.

I see lots of posts about importing single sheets into single tables but nothing about splitting out a single sheet into multiple tables.

Any help would be appreciated.

Regards,

Ken.

 

[Kenneth Hobson]

It is possible using ADO. The method though depends on your needs. It would be easier if you first created each table with the Fields in Access. There are 2 or 3 methods that I know of to create the mdb tables by code. Here again, you must know your fieldnames and field data types to set first.

So, the easiest method is to create the mdb tables, and then run the code to add the xls records for the fields needed for each pre-created table in an mdb file.

For one of the other methods, see this example at vbaexpress.

 

[klong]

Thanks for this. With my very limited vba experience this code looks to create the tables but not import the information.

Were I am struggling is transferring the info. I have the tables ready with the relationships established (only took me a month to do a days work !). The problem I have is how to take for example the Part Number, Price and Quantity into my Tbl_Quote and related Supplier name, Quote reference and Quote Date into the Tbl_Header.

The Relationship between the two tables is via ID that Access likes to put in every table ! The whole database does what I need it to do. It's just populating it now is were I'm stumped.

Ideally I want to get to a point of taking my suppliers quote, from the template I gave them, and import it to the relevant tables using the Append function. If I can figure out how to do it manually then coming up with the vba will be a good learning curve.

 

[Kenneth Hobson]

If that is the case, then it is easy. Here is one method. The key is to select your range with the top row being the column names which are the same as the Access table's fieldnames.

Sub demo()
  Dim objRS As Object, nwindPath As String
  Set objRS = CreateObject("ADODB.Recordset")
  nwindPath = ThisWorkbook.Path & "\nwind.mdb"
  
  Dim r As Range
  [a1] = "LastName"
  [b1] = "FirstName"
  [a2] = "Hobson"
  [b2] = "Kenneth"
  Set r = [a1:b2]
  r.Name = "MyRange"
 
  objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath
 
  Set objRS = Nothing
End Sub

Here is another method to let you pick and choose what you want to add.

Sub ADO()
'   the Microsoft ActiveX Data Objects 2.x Library
    Dim DBFullName As String
    Dim Cnct As String, Src As String
    Dim Connection As ADODB.Connection
    Dim Recordset As ADODB.Recordset
    Dim Col As Integer, Row As Integer, s As String
    
'   Database information
    'DBFullName = "C:\myfiles\vbabook\Names.mdb"
    DBFullName = "u:\Material\ADO\NWind.mdb"
    
'   Open the connection
    Set Connection = New ADODB.Connection
    Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
    Cnct = Cnct & "Data Source=" & DBFullName & ";"
    Connection.Open ConnectionString:=Cnct
    
'   Create RecordSet
    Set Recordset = New ADODB.Recordset
'   Next two lines critical to work in QPro properly.  Excel does not need them.
    Recordset.CursorType = adOpenKeyset
    Recordset.LockType = adLockOptimistic
      
    With Recordset
'       Filter
        Src = "SELECT * FROM Orders "
        'Src = Src & "and CategoryID = 30"
        Recordset.Open Source:=Src, ActiveConnection:=Connection

'   Cells.Clear 'Used in Excel to clear a sheet
'       Write the field names
        'For Col = 0 To .Fields.Count - 1
           'Range("A1").Offset(0, Col).Value = Recordset.Fields(Col).Name   'Excel method
        'Next

If .RecordCount < 1 Then GoTo EndNow 'Query found no matching records
'       Write the recordset by Excel method
        'Range("A1").Offset(1, 0).CopyFromRecordset Recordset
'Add a new record (not pushed to the database until Update)
    MsgBox CStr(.RecordCount), vbInformation, "#Records"
    .AddNew
    Recordset("ShipName") = [Name!A2]
    Recordset("ShipAddress") = [Address!B6]
    Recordset("ShipCity") = Worksheets("City").Range("C3")
    .Update
     MsgBox CStr(.RecordCount), vbInformation, "#Records"
  
  End With
  
    Set Recordset = Nothing
    Connection.Close
    Set Connection = Nothing
End Sub

Form more ADO examples see http://www.erlandsendata.no/english/index.php?t=envbadac

 

[klong]

Thanks Kenneth,

Will give it a try over the weekend and let you know.

Regards,

Ken

 

[klong]

Hi Kenneth,

Struggling with this code. In the line "If .RecordCount < 1 Then GoTo EndNow " I get a compile error, label not defined message when I run it.

I presume if it can be done through vba then there must also be a manual way as well ! Any ideas on this ?