Controlling Access with Excel VBA

[TIML]

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

 

[TIML]

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.

 

[Andy Wiggins]

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.