Full Version: Importing Excel, with lookup tables
UtterAccess Forums > Microsoft® Access > Access Automation
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).
Otried 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?
Hi all
ey I figured it out!
But I got another problem that I'll post in another seperate post.
Here's the 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
            varCat = 0
            varCat = DLookup("[PKCat]", "tlkpCategory", _
                    "[CatCategory] = '" & rst!MaterialCategory & "'")
            If Not IsNull(varCat) Then
                lCat = varCat
                ' Category is OK, check rest of record
                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!MatCategory = varCat
        varMatID = rstout!PKMat
            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
                    varPrice = rst!Price
                    If (IsNull(rst!partnumber)) Then
                        varCode = ""
                        varCode = rst!partnumber
                    End If
                    rstPrice!MatID = varMatID
                    rstPrice!SupplierID = 1
                    rstPrice!MatPriceEffDate = #10/1/2005#
                    rstPrice!MatPrice = varPrice
                    rstPrice!MatProdCode = varCode
            End If
    ' Close the recordset
    Debug.Print "Finished"
End Sub 'ConvertMaterial
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.