Full Version: Importing Excel, with lookup tables
UtterAccess Discussion Forums > Microsoft® Access > Access Automation
robertdagg
Hi all

I have figured out a fair bit of this importing from an excel spreadsheet now thanks to Larry Larsen and some examples he suggested. However I think I'm stuck again.

One of the spreadsheets I want to import has a field that once imported into the mdb will be a lookup link to another table. (Using this in the mdb as we want to tightly control consistency with this field - its the Category field for the items).

I tried importing the spreadsheet and then tried to simply change the mdb field (which imported as a text field) into a lookup, but that just deleted all the field contents.

So .... if I just import the spreadsheet, can I convert the field to a lookup? or do I need to import the spreadsheet and convert the field to a lookup at the same time? or something else?

Thanks
Robert.
robertdagg
Hi all

Hey I figured it out!

But I got another problem that I'll post in another seperate post.

Robert.
robertdagg
Michael

Here's the code:

CODE
  
Option Compare Database
Option Explicit

    Public varMatID As Variant
    
    Public varCat As Variant
    Public lCat As Integer
    
Sub ConvertMaterial()

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim rstout As New ADODB.Recordset
        
    Dim rstPrice As New ADODB.Recordset
        
    Dim strSQL As String
    
    varMatID = ""
    
    varCat = ""
    lCat = 0
        
    strSQL = ""
    
    
   ' Open the connection
   cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=d:\Documents and Settings\My Documents\AAA - Clients\Conversion DBs\Material Conversion db.mdb;"

   ' Open the feed table - Material_Master_List recordset
   rst.Open "SELECT * FROM Master_Material_List", _
      cnn, adOpenForwardOnly, adLockReadOnly
      
   ' Open the output table - tblMaterial recordset
   rstout.Open "SELECT * FROM tblMaterial", _
      cnn, adOpenKeyset, adLockOptimistic
    
   ' Open the output table - tblMatPrice recordset
   rstPrice.Open "SELECT * FROM tblMatPrice", _
      cnn, adOpenKeyset, adLockOptimistic
    
    ' Loop through the feed table and write output records
    Do Until rst.EOF
        If Not IsNull(rst!MaterialCategory) Then
            ' WRITE A RECORD
            
'=========================================================================
' CHECK AND ADD CATEGORY to tlkpCategory
Category:
            varCat = 0
            varCat = DLookup("[PKCat]", "tlkpCategory", _
                    "[CatCategory] = '" & rst!MaterialCategory & "'")
            
            If Not IsNull(varCat) Then
                lCat = varCat
                ' Category is OK, check rest of record
            Else
                DoCmd.SetWarnings False
                strSQL = "Insert INTO tlkpCategory (CatCategory) " _
                    & "VALUES ('" & rst!MaterialCategory & "');"
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
                GoTo Category
            End If
'=========================================================================
        
' CHECK AND ADD PRODUCT to tblMaterial
            
            varProduct = ""
            varProdBid = ""
            
            Select Case (lCat)
                Case 2, 3, 4, 5, 6
                    varProduct = rst!Product
                Case Else
                    varProduct = ""
            End Select
            
            varProdBid = rst!Product
            
'=========================================================================
        
        End If
      
        rstout.AddNew
        rstout!MatCategory = varCat
        rstout.Update
  
        varMatID = rstout!PKMat

'=========================================================================
' CHECK AND ADD PRICE and PRODCODE to tblMatPrice

            varPrice = 0
            varCode = ""
            
            If (((IsNull(rst!Price)) Or (rst!Price = "") Or (rst!Price = 0)) And _
                ((IsNull(rst!partnumber)) Or (rst!partnumber = ""))) Then
                    ' nothing to add - no Price or ProdCode
                Else
                    varPrice = rst!Price
                    
                    If (IsNull(rst!partnumber)) Then
                        varCode = ""
                    Else
                        varCode = rst!partnumber
                    End If
                    
                    rstPrice.AddNew
                    rstPrice!MatID = varMatID
                    rstPrice!SupplierID = 1
                    rstPrice!MatPriceEffDate = #10/1/2005#
                    rstPrice!MatPrice = varPrice
                    rstPrice!MatProdCode = varCode
                    rstPrice.Update
            End If
'=========================================================================
    
        rst.MoveNext
    
    Loop

    ' Close the recordset
    rst.Close
    rstout.Close
    rstPrice.Close
    
    Debug.Print "Finished"
    
End Sub 'ConvertMaterial
'
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.