Create an ACCESS table from Excel
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.
Sandra
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("M
gstrUserId = VBUserName()
dteDate = BOM(Date) - 1
strPath = ThisWorkbook.Worksheets("M
Set appAccess = CreateObject("Access.Appli
' Open the Employees table in the Northwind database
appAccess.OpenCurrentDatab
'*************************
'Create table in database
Set tdfTable = appAccess.CreateTableDef("
With tdfTable
.Fields.Append .CreateField("KMID", dbText)
.Fields.Append .CreateField("Quantity", dbLong)
.Fields.Append .CreateField("Description"
.Fields.Append .CreateField("KMDate", dbDate)
.Fields.Append .CreateField("UserID", dbText)
CurrentDb.TableDefs.Append
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
Sandra
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("M
gstrUserId = VBUserName()
dteDate = BOM(Date) - 1
strPath = ThisWorkbook.Worksheets("M
Set appAccess = CreateObject("Access.Appli
' Open the Employees table in the Northwind database
appAccess.OpenCurrentDatab
'*************************
'Create table in database
Set tdfTable = appAccess.CreateTableDef("
With tdfTable
.Fields.Append .CreateField("KMID", dbText)
.Fields.Append .CreateField("Quantity", dbLong)
.Fields.Append .CreateField("Description"
.Fields.Append .CreateField("KMDate", dbDate)
.Fields.Append .CreateField("UserID", dbText)
CurrentDb.TableDefs.Append
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
Last Comment
ukerandi
You need to open Microsoft access 12.0 object library in Refrences(tools menue)
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
this is realy good example
http://allenbrowne.com/func-dao.html#CreateTableDAO
http://allenbrowne.com/func-dao.html#CreateTableDAO
'Create table in database
Set Db = CurrentDb
Set tdfTable = Db.CreateTableDef("tblTEMP
Set Db = CurrentDb
Set tdfTable = Db.CreateTableDef("tblTEMP
OR
Dim ws as Workspace
Dim db as Database
Set ws=DBEngine.Workspaces(0)
set db=ws.OpenDatabase({databa
Set tdfTable = Db.CreateTableDef("tblTEMP
Dim ws as Workspace
Dim db as Database
Set ws=DBEngine.Workspaces(0)
set db=ws.OpenDatabase({databa
Set tdfTable = Db.CreateTableDef("tblTEMP
ASKER
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.
Sandra
Sandra
Microsoft Excel
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
