access[Resolved]

Nope, That didn't work.

Does the rsXL recordcount match the actual excel rowcount?

Try it in a new test workbook with 10 rows of new typed in data. It worked fine in my test project, perhaps its a
data dependant issue?

I got it to pop up a message box with the number that is stored in the iCount Variable.

VB Code:

iCount = rsXl.RecordCount

There are 6 rows in the spreadsheet. But it displayed 5 in the message box.

Are your excel rows contiguous?

Did you try the test on a new workbook with test data like I suggested?

I have no idea if they are contigous or not. and yes I tried what you told me and still the same thing....for some reason it's skipping the first row.

I mean, are all the rows one right below each other or are there blank rows in between?

Theres no spaces in between them.

Ok, this has got to be it! :D

VB Code:

cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;[b]HDR=No;[/b]"

The "HDR=No;" part tells that there is no column header information in the first row, only data.

I put it in and the following error comes up.

Could not find installable ISAM.

Hmm, its straight from connectionstrings.com. But this sounds like the issue. Excel is skipping the first row
thinking it contains column header information.

I'm sure I'll work something out...Now is there a way to alter the insert statement bacause not every spreadsheet/Database will have 4 columns.

I knew that was coming :D

We just need to build the string of fields by looping through the columns collection.

VB Code:

Dim sFields as string
    sFields = vbNullString
    For i = 0 to rsXL.Fields.Count - 1
        sFields = sFields & " " & rsXl.Fields(i).Name& ","
    Next
    sFields = Left$(sFields, Len(sFields) -1)'Trim off the last comma
    Dim iValues as integer
    Dim sValues as string
    For i = 1 To rsXl.RecordCount
        'Build the values to be inserted string
        sValues = vbnullstring
        For iValues = 0 to rsXL.Fields.Count - 1
            sValues = sValues & rsXL.Fields(iValues).Value & "," 'ToDo: add single quotes if the field is a text value
        Next
        sValues = Left$(sValues, Len(sValues) -1)'Trim off the last comma
        moCnn.Execute "INSERT INTO Table1 (" & sFields & ") VALUES ('" & sValues & "')", lRecs
        rsXl.MoveNext
    Next

So that just replaces the insert statement or is there a special spot where each bit of code goes...:P

It replaces the For Loop that loops Inserting the records.
You can put in after the Else statement block...

VB Code:

Else
        'Read the data that is in the first sheet and add it to Access by way of
        'the recordset set to the Access table - Table1, one record at a time
 
        'INSERT CODE HERE AND TAKE OUT FOR LOOP ;)

The following code is highlighted in red and I think it has to do with the comma in the qoutation marks at the end of it

VB Code:

sFields = sFields & " " & rsXl.Fields(i).Name& ","

Its the ampersand char next to the field.

VB Code:

sFields = sFields & " " & rsXl.Fields(i).[b]Name&[/b] ","
 
'Should be...
sFields = sFields & " " & rsXl.Fields(i).Name & ","

VB Code:

Dim sFields as string
    sFields = vbNullString
    For i = 0 to rsXL.Fields.Count - 1
        sFields = sFields & " " & rsXl.Fields(i).Name& "," ' <---This line here...Its red and it says its an expected end of statement. Not sure why though.
    Next
    sFields = Left$(sFields, Len(sFields) -1)'Trim off the last comma
    Dim iValues as integer
    Dim sValues as string
    For i = 1 To rsXl.RecordCount
        'Build the values to be inserted string
        sValues = vbnullstring
        For iValues = 0 to rsXL.Fields.Count - 1
            sValues = sValues & rsXL.Fields(iValues).Value & "," 'ToDo: add single quotes if the field is a text value
        Next
        sValues = Left$(sValues, Len(sValues) -1)'Trim off the last comma
        moCnn.Execute "INSERT INTO Table1 (" & sFields & ") VALUES ('" & sValues & "')", lRecs
        rsXl.MoveNext
    Next

It was a copy/paste issue when I put the code into the reply window. See post #56 for the correction.

Lol. For some reason I totally missed that post. Thanks..But...When i run it I get the same: Number of querys values and destination fields are not the same error, and it points to the Insert line. >_<

Then we need to determine if its true. You have your Access table fields named the same and same
order and same count as the columns in Excel?

Yep...I'm pretty sure they all match.

Do this to get the actual insert statement. Add the debug.print ... in this location of the code.

VB Code:

Debug.Print "INSERT INTO Table1 (" & sFields & ") VALUES ('" & sValues & "')"
Stop 'Stops the code (like a breakpoint) so you can check the immediate window results.
moCnn.Execute

and post the actual insert statement.

A box comes up that says argument not optional and when i click ok it highlights the .excecute of moCnn.Execute

You need to stop on the Stop line and read the immediate window. I didnt post all the code so the
moCnn.execute should stay the same. It was for reference on where to place the debug.print statement.

INSERT INTO Table1 ( Name, Age, Birthdate, Salary) VALUES ('Ryan,20,1984,34000')

Ah Ha! Thats it. The single quotes are only around the entire delimeted list. This makes it think there is only
one value to insert. Remember I posted the code with a omment about this being a "ToDo".

When the values are being built we need to update the code to add a single quote around the values that are string type only.

Are they all Text type?

In the database. the types are all text if thats what you mean?

Yes thats what I needed to know. This will make it easier to import then.

VB Code:

For i = 1 To rsXl.RecordCount
        'Build the values to be inserted string
        sValues = vbnullstring
        For iValues = 0 to rsXL.Fields.Count - 1
            sValues = sValues & rsXL.Fields(iValues).Value & "','" 'Added the single quote but hard to see - " ' , ' ".
        Next
        sValues = Left$(sValues, Len(sValues) -2)'Trim off the last comma and single quote
        moCnn.Execute "INSERT INTO Table1 (" & sFields & ") VALUES ('" & sValues & ")", lRecs 'Removed the last single quote so its not duplicated
        rsXl.MoveNext
    Next

The helps but When I run it now and select both the excel file and the access file then click the import button the following Run-time error pops up

The INSERT INTO statement contains the following unknown field name: 'salary'. Make sure you have typed the name correctly, and try the operation again.

I dont recall it being case sensitive but can you change excels column to "Salary" and test?

I got it working...I had 'Income' in the access table instead of 'Salary'. It didn't copy the first row from excel but that is probably because they are the same as the column names in access...maybe...I dunno.

I also tried it with another excel and access file with a different ammount of columns and it worked Great...:D

Doh!

w00t w00t!
At least its finally working. Was this a long thread or what :D

:thumb:

Now I have another question...I already posted it somewheres else but gave up on it cause they weren't helpin very much so I thought I'd ask you. After it writes everything to the database, if I wanna do it again right after I have to manually go to the database and delete all the records, if not I get an duplicating error. Is there a way I can delete all the records or some other way to get around this.

From your ADO connection object you can execute a sql statement to delete all or partial records.

VB Code:

moCnn.Execute "DELETE FROM Table1;", lRecs
'Or
moCnn.Execute "DELETE FROM Table1 WHERE Something = somethingelse;", lRecs

Sweet Thanks ^_^

:D

You can check the lRecs after execution to see how many records were deleted.

VB Code:

moCnn.Execute "DELETE FROM Table1;", lRecs
MsgBox lRecs & " - Records deleted!", vbOkOnly + vbInformation

I like :D...I used that same code to tell the user how many records were imported.

Alright...i know want to "improve" it a bit by allowing the user to select with sheet to import into which table. Like When i open the two files and hit a load button, the sheets/tables will be put into a drop down menu thingy and then I select which one I want from each list then import what i've selected.

You would need to connect to each workbook and open a schema recordset to get a listing of its sheets.
Load the combo, close the objects, connect to anyother selections and do the same.

Then when you go to open the selected sheet, re-connect to it and open a rs of that sheets contents.

:ehh: - That sounds like a lot of work.