|
Controlling Access with Excel VBA
I am trying to populate an Access table with Excel data. I am using Office
2000. The following code, running in my excel workbook, works off and on but is not consistent. Any thoughts would be greatly appreaciated. Dim db As Database, rs As Recordset, RFound As Boolean, JVar As Variant, DVar As Variant, MyFile, MyDate As Date Application.DisplayAlerts = False Application.ScreenUpdating = False On Error Resume Next Set db = OpenDatabase("\\GEORGE\Public\Databases\Production \Secure\Production.mdb") ' open the database Set rs = db.OpenRecordset("ProductionbySC", dbOpenTable) ' get all records in a table JVar = Range("B3").Value DVar = Range("B2").Value If JVar = "" Then MsgBox "There is no Julian Date, Please enter the Julian Date", vbOKOnly Cancel = True Exit Sub End If If DVar = "" Then MsgBox "There is no Date, Please enter the Date", vbOKOnly Cancel = True Exit Sub End If ' Clear out Detail Page Sheets("Details").Select Range("A2:M2").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents ' Copy to Detail Sheet Sheets("Main").Select Range("C6:O33").Select Selection.Copy Sheets("Details").Select Range("A2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A2").Select i = 0 Do i = i + 1 If ActiveCell.Value = "" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop Until i = 29 Range("A1").Select ActiveWorkbook.Names("Detail").Delete Range("A1:M1").Select Range(Selection, Selection.End(xlDown)).Name = "Detail" MyFile = ActiveWorkbook.FullName MyDate = Sheets("Main").Range("B2") Sql = "DELETE * FROM ProductionbySC WHERE Date = " & MyDate ''''''' Doesn't Work DoCmd.RunSQL (Sql) ''''''' Doesn't Work DoCmd.TransferSpreadsheet acImport, 5, "ProductionbySC", MyFile, True, "Detail" ''''''' Doesn't Work all the time rs.Close Set rs = Nothing db.Close |
Controlling Access with Excel VBA
What is happening is that when I have the database open, the code works
fine, but when the database is not open, it doesn't work. "TIML" wrote in message ... I am trying to populate an Access table with Excel data. I am using Office 2000. The following code, running in my excel workbook, works off and on but is not consistent. Any thoughts would be greatly appreaciated. Dim db As Database, rs As Recordset, RFound As Boolean, JVar As Variant, DVar As Variant, MyFile, MyDate As Date Application.DisplayAlerts = False Application.ScreenUpdating = False On Error Resume Next Set db = OpenDatabase("\\GEORGE\Public\Databases\Production \Secure\Production.mdb") ' open the database Set rs = db.OpenRecordset("ProductionbySC", dbOpenTable) ' get all records in a table JVar = Range("B3").Value DVar = Range("B2").Value If JVar = "" Then MsgBox "There is no Julian Date, Please enter the Julian Date", vbOKOnly Cancel = True Exit Sub End If If DVar = "" Then MsgBox "There is no Date, Please enter the Date", vbOKOnly Cancel = True Exit Sub End If ' Clear out Detail Page Sheets("Details").Select Range("A2:M2").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents ' Copy to Detail Sheet Sheets("Main").Select Range("C6:O33").Select Selection.Copy Sheets("Details").Select Range("A2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A2").Select i = 0 Do i = i + 1 If ActiveCell.Value = "" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop Until i = 29 Range("A1").Select ActiveWorkbook.Names("Detail").Delete Range("A1:M1").Select Range(Selection, Selection.End(xlDown)).Name = "Detail" MyFile = ActiveWorkbook.FullName MyDate = Sheets("Main").Range("B2") Sql = "DELETE * FROM ProductionbySC WHERE Date = " & MyDate ''''''' Doesn't Work DoCmd.RunSQL (Sql) ''''''' Doesn't Work DoCmd.TransferSpreadsheet acImport, 5, "ProductionbySC", MyFile, True, "Detail" ''''''' Doesn't Work all the time rs.Close Set rs = Nothing db.Close |
Controlling Access with Excel VBA
This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries. http://www.bygsoftware.com/examples/sql.html Or you can get there from the "Excel with Access Databases" section on page: http://www.bygsoftware.com/examples/examples.htm It demonstrates how to use SQL in Excel's VBA to: * create a database, * create a table * insert records * select records, * update records, * delete records, * delete a table, * delete a database. DAO and ADO files available. You can also download the demonstration file called "excelsql.zip". The code is open and commented. -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "TIML" wrote in message ... What is happening is that when I have the database open, the code works fine, but when the database is not open, it doesn't work. "TIML" wrote in message ... I am trying to populate an Access table with Excel data. I am using Office 2000. The following code, running in my excel workbook, works off and on but is not consistent. Any thoughts would be greatly appreaciated. Dim db As Database, rs As Recordset, RFound As Boolean, JVar As Variant, DVar As Variant, MyFile, MyDate As Date Application.DisplayAlerts = False Application.ScreenUpdating = False On Error Resume Next Set db = OpenDatabase("\\GEORGE\Public\Databases\Production \Secure\Production.mdb") ' open the database Set rs = db.OpenRecordset("ProductionbySC", dbOpenTable) ' get all records in a table JVar = Range("B3").Value DVar = Range("B2").Value If JVar = "" Then MsgBox "There is no Julian Date, Please enter the Julian Date", vbOKOnly Cancel = True Exit Sub End If If DVar = "" Then MsgBox "There is no Date, Please enter the Date", vbOKOnly Cancel = True Exit Sub End If ' Clear out Detail Page Sheets("Details").Select Range("A2:M2").Select Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents ' Copy to Detail Sheet Sheets("Main").Select Range("C6:O33").Select Selection.Copy Sheets("Details").Select Range("A2").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A2").Select i = 0 Do i = i + 1 If ActiveCell.Value = "" Then Selection.EntireRow.Delete Else ActiveCell.Offset(1, 0).Select End If Loop Until i = 29 Range("A1").Select ActiveWorkbook.Names("Detail").Delete Range("A1:M1").Select Range(Selection, Selection.End(xlDown)).Name = "Detail" MyFile = ActiveWorkbook.FullName MyDate = Sheets("Main").Range("B2") Sql = "DELETE * FROM ProductionbySC WHERE Date = " & MyDate ''''''' Doesn't Work DoCmd.RunSQL (Sql) ''''''' Doesn't Work DoCmd.TransferSpreadsheet acImport, 5, "ProductionbySC", MyFile, True, "Detail" ''''''' Doesn't Work all the time rs.Close Set rs = Nothing db.Close |
| All times are GMT +1. The time now is 05:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com