Excell and Access - blank records?

[adamr99]

I am trying to import data from excell into access. I am importing a small amount of data from a lot of spreadsheets (more than 700).

Unfortunately, what it does, is inserts in the following format:

166 full data records (the correct data too)
79 BLANK records
1 record with data
79 BLANK records
1 record with data
79 BLANK records
1 record with data
79 BLANK records
121 records with data
79 BLANK records
1 record with data
79 BLANK records


there seems to be a pattern with one exception in the middle there...... I have tried about everything to solve this problem.

I am running some VBA code that gets the spreadsheet's URL from another "path" spreadsheet, then opens them up and grabs the data. HELP PLEASE!!

ADAM
HEre is the code:


Option Compare Database
Option Explicit


Private Sub Command0_Click()

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object

Dim objXL_path As Object
Dim xlWB_path As Object
Dim xlWS_path As Object
'some objects to refer to Excel

Dim db As DAO.Database
Dim sample_info As DAO.Recordset
Dim species_comp As DAO.Recordset
Dim bio_vol As DAO.Recordset
Dim letter_look As DAO.Recordset
Dim sample_look As DAO.Recordset
Dim genus_look As DAO.Recordset
Dim percent_done As Double
Dim sample_date As Date
Dim Sample_ID As String 'string to hold the sampleid as it is being generated
Dim Job_ID As String
Dim Lake_ID As Integer
Dim i As Integer
Dim j As Integer
Dim data_file As String
Dim data_sheet As String


Set db = CurrentDb
Set sample_info = db.OpenRecordset("TEST_Sample_Info_FINAL&quot 'open our recordsets
Set letter_look = db.OpenRecordset("TEST_Letter_Lookup&quot
Set sample_look = db.OpenRecordset("Sample_ID_Master&quot
Set genus_look = db.OpenRecordset("Genus_lookup&quot



Set objXL_path = CreateObject("Excel.Application&quot
Set xlWB_path = objXL_path.workbooks.Open("C:\!SECLIMNO_USER\Adam\path.xls&quot 'THE PATH SPREADSHEE FOR THE OTHERS
Set xlWS_path = xlWB_path.worksheets("Sheet1&quot

j = 1
With xlWS_path

data_file = (.range("A" + CStr(j)))
data_sheet = (.range("B" + CStr(j)))

End With

Do While (data_file <> &quot;&quot

Me.Text6.SetFocus
Me.Text6.Text = data_file
Me.Text8.SetFocus
Me.Text8.Text = data_sheet


Set objXL = CreateObject(&quot;Excel.Application&quot
Set xlWB = objXL.workbooks.Open(data_file)
Set xlWS = xlWB.worksheets(data_sheet)
'set references to our excel worksheet
Me.Text1.SetFocus
Me.Text1.Text = &quot;&quot;
Me.Text3.SetFocus
Me.Text3.Text = &quot;&quot;


'this is the manipulation of the spreadsheet for the Sample_Information_Final table
With xlWS

'*******************fill sample_info************************************
sample_info.AddNew 'create the new record in sample_info_final
sample_info.Update
sample_info.Edit
sample_info.Update

sample_info.MoveLast 'edit the newly created record
sample_info.Edit

sample_date = .range(&quot;B2&quot
Lake_ID = .range(&quot;B1&quot

sample_look.MoveFirst

Dim isnotfound As Boolean
isnotfound = True

While (isnotfound)
If (sample_date = sample_look.Fields(4)) Then
If (Lake_ID = CInt(sample_look.Fields(1))) Then
isnotfound = False

Else
sample_look.MoveNext
End If


Else
sample_look.MoveNext
End If

Wend



Sample_ID = sample_look.Fields(0)
Job_ID = &quot;PHYTO&quot; + Sample_ID
.range(&quot;K109&quot = &quot;Cyanophycae&quot;
.range(&quot;K124&quot = &quot;Dinobryon&quot;
xlWB.Save
sample_info.Fields(1) = Job_ID 'fill sample_information_FINAL
sample_info.Fields(2) = Sample_ID
sample_info.Fields(3) = (.range(&quot;C2&quot * 1000)
sample_info.Fields(4) = .range(&quot;D2&quot
sample_info.Fields(5) = .range(&quot;E2&quot
sample_info.Fields(6) = .range(&quot;N106&quot
sample_info.Fields(7) = 0
sample_info.Fields(8) = &quot;416822-1&quot;
sample_info.Fields(9) = 200
sample_info.Fields(10) = &quot;XX&quot;
sample_info.Fields(11) = &quot;XX&quot;
'field 11 is automatically set to NO

sample_info.Update




End With
percent_done = 0
j = j + 1
With xlWS_path
data_file = .range(&quot;A&quot; + CStr(j))
data_sheet = .range(&quot;B&quot; + CStr(j))

End With




'*******************close the files************************************


xlWB.Close
objXL.Quit


Set xlWS = Nothing
Set xlWB = Nothing
Set objXL = Nothing

Loop

xlWB_path.Close
objXL_path.Quit


Set xlWS_path = Nothing
Set xlWB_path = Nothing
Set objXL_path = Nothing

sample_info.Close

letter_look.Close
sample_look.Close
genus_look.Close


Set sample_info = Nothing
Set species_comp = Nothing
Set bio_vol = Nothing
Set letter_look = Nothing
Set sample_look = Nothing
Set genus_look = Nothing

Set db = Nothing
'tidy up time


MsgBox (&quot;Successful Run&quot


End Sub

 

[adamr99]

For anyone searching this - the solution that I went with was to simply populate some empty text boxes on my form with the data that I wanted to add, and then run an append query to add the data using the text box data instead of trying to add it in VBA.

Good Luck!

Adam