I have been reading some articles on the site and I can't really find out how to make them work with what I have. I need some help.
Here is what I have -
It keeps failing at the Records.EDIT
What am I missing? - Dim appExcel As Object
-
Dim workBook As Object
-
Dim workSheet As Object
-
Dim i As Integer
-
Dim FdrID As Integer
-
Dim dbs_curr As Database
-
Dim records As Recordset
-
Dim sqlStatement As String
-
Dim Message As String
-
Set dbs_curr = CurrentDb
-
Dim returnValue As Integer
-
-
-
Select Case returnValue
-
Case vbCancel:
-
Exit Function
-
Case vbNo
-
Application.FollowHyperlink "http://powerdelivery.southernco.com/apc/distribution/eng-svcs/SGIG/SGIG.html", , True, True
-
Exit Function
-
Case vbYes
-
-
End Select
-
-
-
-
DoCmd.OpenForm "frmRunningQuery"
-
Forms!frmRunningQuery.Repaint
-
-
'Delete all existing CSS informatin
-
DoCmd.SetWarnings False
-
DoCmd.RunSQL "UPDATE tblTest SET tblTest.feederID = Null, tblTest.Description = Null, tblTest.CSSCustomers = Null, tblTest.CSSRevenue = Null, tblTest.ADDSCircuitMiles = Null, tblTest.SAIFI = Null, tblTest.SAIDI = Null, tblTest.MAIFI = Null, tblTest.OpCenter = Null, tblTest.Region = Null, tblTest.SubstationUnit = Null, tblTest.NextDate = Null, tblTest.CompleteDate = Null, tblTest.StartDate = Null, tblTest.ReviewDate = Null, tblTest.ReviewedDate = Null, tblTest.TotalPoles = Null;"
-
DoCmd.SetWarnings True
-
-
-
' Open an existing spreadsheet
-
Set appExcel = GetObject("C:\CSS.xls")
-
-
-
Set workSheet = appExcel.Worksheets(1)
-
-
sqlStatement = "SELECT tblTest.* FROM tblTest"
-
Set records = dbs_curr.OpenRecordset(sqlStatement, dbOpenDynaset, dbSeeChanges, dbOptimistic)
-
-
records.Edit
-
records!feederID = workSheet.Cells.Range("B" & i & ":B" & i).Value
-
records!Description = workSheet.Cells.Range("C" & i & ":C" & i).Value
-
records!CSSCustomers = workSheet.Cells.Range("D" & i & ":D" & i).Value
-
records!CSSRevenue = workSheet.Cells.Range("E" & i & ":E" & i).Value
-
records!ADDSCircuitMiles = workSheet.Cells.Range("F" & i & ":F" & i).Value
-
records!SAIFI = workSheet.Cells.Range("G" & i & ":G" & i).Value
-
records!SAIDI = workSheet.Cells.Range("H" & i & ":H" & i).Value
-
records!MAIFI = workSheet.Cells.Range("I" & i & ":I" & i).Value
-
records!OpCenter = workSheet.Cells.Range("K" & i & ":K" & i).Value
-
records!Region = workSheet.Cells.Range("M" & i & ":M" & i).Value
-
records!SubstationUnit = workSheet.Cells.Range("P" & i & ":P" & i).Value
-
records!NextDate = workSheet.Cells.Range("R" & i & ":R" & i).Value
-
records!CompleteDate = workSheet.Cells.Range("S" & i & ":S" & i).Value
-
records!StartDate = workSheet.Cells.Range("T" & i & ":T" & i).Value
-
records!ReviewDate = workSheet.Cells.Range("U" & i & ":U" & i).Value
-
records!ReviewedDate = workSheet.Cells.Range("V" & i & ":V" & i).Value
-
records!TotalPoles = workSheet.Cells.Range("W" & i & ":W" & i).Value
-
records.Update
-
-
' Release objects
-
Set workSheet = Nothing
-
Set workBook = Nothing
-
Set appExcel = Nothing
-
-
DoCmd.Close acForm, "frmRunningQuery"
-
MsgBox "Done"
-
-
End Function
-
-
Line 31, that's one way... - 'Replace line 31
-
zjsql = "DELETE * " _
-
& "FROM tbl_nametoclear;"
-
db.Execute zjsql, dbFailOnError
Also, it is, IMHO, better to build your string first, then use it in the commands. This way, if something wierd happens, or doesn't happen, all one need do is place a debug.pring strSQL after the variable to see what is actually being resolved in the string. However, when building the string within the command, as you have in your code, there is no easy way to determine what is happening with it.
Line 44 WILL throw this error because in Line 31 you just cleared this table; therefore, there is nothing to edit. You will need to add a new record as jimatqsi indicated.
Next what is up with all of these "i" in your ranges such as: workSheet.Cells.Range("B" & i & ":B" & i).Value
You are not in a loop nor have you defined the value for "i" that I can find so i=0; therefore, the above string resolves to: workSheet.Cells.Range("B0:B0").Value
You really need to look at the transferworksheet method. Pull your worksheet into a temporary table and then parse that table as needed to validate datatypes and entries.
Here are three threads that have information and links covering different uses for this method: >>updating-access-using-excel-spreadsheets-used-distribute-access-info >> how-export-data-form-datasheet-excel-via-code >>import-excel-cells-table-but-field-names-different-both
finally as jimatqsi pointed out, you need to close the record set first before setting it to nothing.
You also need to release the Execl objects.
5 1972
What error are you getting (number and exact message)?
Run-time error '3021':
No Current record
it does that at the
Doesn't make it anywhere
Your table must be empty, the recordset returned by the Select is returning an empty recordset. You have to check for that before doing the .Edit. Change records.Edit to the following: - if not records.eof then
-
records.Edit
-
else
-
records.AddNew
-
end if
-
Jim
Also when you are releasing objects you should have: - records.close
-
set records = nothing
-
zmbd 5,501
Expert Mod 4TB
Line 31, that's one way... - 'Replace line 31
-
zjsql = "DELETE * " _
-
& "FROM tbl_nametoclear;"
-
db.Execute zjsql, dbFailOnError
Also, it is, IMHO, better to build your string first, then use it in the commands. This way, if something wierd happens, or doesn't happen, all one need do is place a debug.pring strSQL after the variable to see what is actually being resolved in the string. However, when building the string within the command, as you have in your code, there is no easy way to determine what is happening with it.
Line 44 WILL throw this error because in Line 31 you just cleared this table; therefore, there is nothing to edit. You will need to add a new record as jimatqsi indicated.
Next what is up with all of these "i" in your ranges such as: workSheet.Cells.Range("B" & i & ":B" & i).Value
You are not in a loop nor have you defined the value for "i" that I can find so i=0; therefore, the above string resolves to: workSheet.Cells.Range("B0:B0").Value
You really need to look at the transferworksheet method. Pull your worksheet into a temporary table and then parse that table as needed to validate datatypes and entries.
Here are three threads that have information and links covering different uses for this method: >>updating-access-using-excel-spreadsheets-used-distribute-access-info >> how-export-data-form-datasheet-excel-via-code >>import-excel-cells-table-but-field-names-different-both
finally as jimatqsi pointed out, you need to close the record set first before setting it to nothing.
You also need to release the Execl objects.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bonnie R |
last post by:
Hello!
I am writing VBA code in Access 97 to import an Excel 2000
Spreadsheet. I had hoped to do this using ODBC, but that was not to
be, for who knows what reason.
The problem is that I import...
|
by: Don W. Reynolds |
last post by:
Hi All,
I am sent an excel spreadsheet on a daily basis. Sometimes it contains 10
rows; sometimes it contains over 5000 rows. I copy this spreadsheet into
another spreadsheet and verify the...
|
by: T2 |
last post by:
I need some code ideas to import Excel into Access using
VB. I need to use VB because the Excel column formats are
not always consistent.
I'm importing a fairly simple spreadsheet.
Any...
|
by: Miguel Ramirez |
last post by:
Hello all,
Is there any way to import an excel file data from user's computer?
I want to read all records in the excel file and write the data in a SQL
server table.
Any help will be really...
|
by: christiekp |
last post by:
I am able to import a file from excel to access manually, using the top row as my headings, successfully. however, when using the TransferSpreadsheet command in a macro, importing, excel 5-7, has...
|
by: coolcoder2007 |
last post by:
Hi,
I have Exported some data in a Excel file using this code-
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=dependencymatrix.xls");
...
|
by: Shootah |
last post by:
Hi,
I have succeeded in adding automated relationships with refference tables after importing an excel file created from a query to an Access database.
However I have the following problem: ...
|
by: maryanncanor |
last post by:
Hi, I have an input excel file that needs to be imported to Table1. However, the fieldnames or header in the excel file doesnt match the fieldnames in the database. The fieldnames in the excel file...
|
by: anand padia |
last post by:
I have a master access table where we store all the employee information. I have various application developed in excel which imports and uses information in master.
Now I want to develop a excel...
|
by: Ehsan arman |
last post by:
Hi guys,
I have a bunch of excel files stored in a folder and have a code to choose the appropriate excel sheet by opening up a dialog box. However, each excel sheet has a row which include the...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
| |