Create an ACCESS table from Excel

Sandra Smith
Sandra Smith used Ask the Experts™
I seem to be going in circles.  I get some to work and then it doesn't.  The below partially works - however, the code between the asterisk lines does not.  I need to create the table in the databased from Excel, but it does not recognie my appACCESS.


Public Sub OpenKeyMetricsForm()
'Opens Key Metrics form for data entry into the database
On Error Resume Next
Dim strPath As String
Dim appAccess As Access.Application
Dim tdfTable As DAO.TableDef
Dim dteDate As Date
Dim gstrUserId As String
Dim intDeptID As Long

intDeptID = ThisWorkbook.Worksheets("MainMenu").Range("DeptID")
gstrUserId = VBUserName()
dteDate = BOM(Date) - 1

strPath = ThisWorkbook.Worksheets("MainMenu").Range("B1")

    Set appAccess = CreateObject("Access.Application")
   ' Open the Employees table in the Northwind database
   appAccess.OpenCurrentDatabase strPath
 'Create table in database

Set tdfTable = appAccess.CreateTableDef("tblTEMPKMQuantities")
With tdfTable
    .Fields.Append .CreateField("KMID", dbText)
    .Fields.Append .CreateField("Quantity", dbLong)
    .Fields.Append .CreateField("Description", dbText)
    .Fields.Append .CreateField("KMDate", dbDate)
    .Fields.Append .CreateField("UserID", dbText)
    CurrentDb.TableDefs.Append tdfTable
End With

'Insert KM information
DoCmd.RunSQL "INSERT INTO tblTEMPKMQuantities ( KMID, Description, KMDate, UserID)" & _
    "SELECT tblKM.KMID, tblKM.Description, #" & dteDate & "#, '" & gstrUserId & "' " & _
    "FROM tblKM WHERE tblKM.Active = -1 AND tblKM.DeptID = " & DeptID
   ' Open KM form
    ' appAccess.DoCmd.OpenForm "frmKeyMetricsQtys", acViewNormal
     appAccess.DoCmd.Close acForm, "frmTimeSheet"
     appAccess.Visible = True
     appAccess.DoCmd.OpenForm "frmKeyMetricsQty", acViewNormal

End Sub
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

You need to open Microsoft access 12.0 object library in Refrences(tools menue)
open in the references
Microsoft DAO 3.6 Object library
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

'Create table in database
Set Db = CurrentDb
Set tdfTable = Db.CreateTableDef("tblTEMPKMQuantities")


Dim ws as Workspace
Dim db as Database

Set ws=DBEngine.Workspaces(0)
set db=ws.OpenDatabase({databasepath and name})

Set tdfTable = Db.CreateTableDef("tblTEMPKMQuantities")
Sandra SmithRetired


Totally overlooked the reference.  I have not worked in Excel for quite some time so did not think about the library reference.  Thank you for the same and suggestion.  there will be more questions as i work on this project as I have forgotten much of my Excel VBA relative to working with ACCESS.  


Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial