473,320 Members | 1,969 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need help importing excel file to Access db table

anoble1
245 128KB
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?

Expand|Select|Wrap|Line Numbers
  1. Dim appExcel As Object
  2.     Dim workBook As Object
  3.     Dim workSheet As Object
  4.     Dim i As Integer
  5.     Dim FdrID As Integer
  6.     Dim dbs_curr As Database
  7.     Dim records As Recordset
  8.     Dim sqlStatement As String
  9.     Dim Message As String
  10.     Set dbs_curr = CurrentDb
  11.     Dim returnValue As Integer
  12.  
  13.  
  14. Select Case returnValue
  15.     Case vbCancel:
  16.     Exit Function
  17.     Case vbNo
  18.     Application.FollowHyperlink "http://powerdelivery.southernco.com/apc/distribution/eng-svcs/SGIG/SGIG.html", , True, True
  19.     Exit Function
  20.     Case vbYes
  21.  
  22.     End Select
  23.  
  24.  
  25.  
  26.     DoCmd.OpenForm "frmRunningQuery"
  27.     Forms!frmRunningQuery.Repaint
  28.  
  29. 'Delete all existing CSS informatin
  30.     DoCmd.SetWarnings False
  31.     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;"
  32.     DoCmd.SetWarnings True
  33.  
  34.  
  35.     ' Open an existing spreadsheet
  36.     Set appExcel = GetObject("C:\CSS.xls")
  37.  
  38.  
  39.     Set workSheet = appExcel.Worksheets(1)
  40.  
  41. sqlStatement = "SELECT tblTest.* FROM tblTest"
  42.             Set records = dbs_curr.OpenRecordset(sqlStatement, dbOpenDynaset, dbSeeChanges, dbOptimistic)
  43.  
  44. records.Edit
  45.                 records!feederID = workSheet.Cells.Range("B" & i & ":B" & i).Value
  46.                 records!Description = workSheet.Cells.Range("C" & i & ":C" & i).Value
  47.                 records!CSSCustomers = workSheet.Cells.Range("D" & i & ":D" & i).Value
  48.                 records!CSSRevenue = workSheet.Cells.Range("E" & i & ":E" & i).Value
  49.                 records!ADDSCircuitMiles = workSheet.Cells.Range("F" & i & ":F" & i).Value
  50.                 records!SAIFI = workSheet.Cells.Range("G" & i & ":G" & i).Value
  51.                 records!SAIDI = workSheet.Cells.Range("H" & i & ":H" & i).Value
  52.                 records!MAIFI = workSheet.Cells.Range("I" & i & ":I" & i).Value
  53.                 records!OpCenter = workSheet.Cells.Range("K" & i & ":K" & i).Value
  54.                 records!Region = workSheet.Cells.Range("M" & i & ":M" & i).Value
  55.                 records!SubstationUnit = workSheet.Cells.Range("P" & i & ":P" & i).Value
  56.                 records!NextDate = workSheet.Cells.Range("R" & i & ":R" & i).Value
  57.                 records!CompleteDate = workSheet.Cells.Range("S" & i & ":S" & i).Value
  58.                 records!StartDate = workSheet.Cells.Range("T" & i & ":T" & i).Value
  59.                 records!ReviewDate = workSheet.Cells.Range("U" & i & ":U" & i).Value
  60.                 records!ReviewedDate = workSheet.Cells.Range("V" & i & ":V" & i).Value
  61.                 records!TotalPoles = workSheet.Cells.Range("W" & i & ":W" & i).Value
  62.                 records.Update
  63.  
  64. ' Release objects
  65.     Set workSheet = Nothing
  66.     Set workBook = Nothing
  67.     Set appExcel = Nothing
  68.  
  69.     DoCmd.Close acForm, "frmRunningQuery"
  70.     MsgBox "Done"
  71.  
  72. End Function
  73.  
  74.  
Aug 28 '13 #1

✓ answered by zmbd

Line 31, that's one way...
Expand|Select|Wrap|Line Numbers
  1. 'Replace line 31
  2. zjsql = "DELETE * " _
  3. & "FROM tbl_nametoclear;"
  4. 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
Seth Schrock
2,965 Expert 2GB
What error are you getting (number and exact message)?
Aug 28 '13 #2
anoble1
245 128KB
Run-time error '3021':
No Current record


it does that at the
Expand|Select|Wrap|Line Numbers
  1. records.Edit
Doesn't make it anywhere
Aug 28 '13 #3
jimatqsi
1,271 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. if not records.eof then
  2.     records.Edit
  3. else
  4.     records.AddNew
  5. end if
  6.  
Jim
Aug 28 '13 #4
jimatqsi
1,271 Expert 1GB
Also when you are releasing objects you should have:
Expand|Select|Wrap|Line Numbers
  1. records.close
  2. set records = nothing
  3.  
Aug 28 '13 #5
zmbd
5,501 Expert Mod 4TB
Line 31, that's one way...
Expand|Select|Wrap|Line Numbers
  1. 'Replace line 31
  2. zjsql = "DELETE * " _
  3. & "FROM tbl_nametoclear;"
  4. 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.
Aug 28 '13 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

7
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...
2
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...
2
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...
3
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...
1
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...
1
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"); ...
0
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: ...
5
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...
12
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...
43
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
0
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...
1
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)...
0
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...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.