Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 39
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access 2002 VB Module controlling Excel 2002 spreadsheet -Run-time Mblackmore Excel Programming 0 May 12th 05 07:56 PM
Controlling IE from Excel Chrispy Excel Programming 1 May 3rd 05 11:55 AM
Controlling Access from Excel John Bigness Excel Programming 2 April 2nd 05 01:09 AM


All times are GMT +1. The time now is 07:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"