If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I want to insert data from excel to access with some requirement:
Field UserName show the user is logged in
Field Date show date/time which the user is logged in
Field Status is have 2 condition:
First, if you have updated StudentID, the Status is Active and the rest of parameter is update.
Second, if you do not update StudentID, you update all of them.

I hope everybody help me to resolve my lesson!!!!!!!!!!
Thanks for everybody!!!!!!!!!!

Re: Export Excel to 2 tables Access ??????

You should first remove (or outcomment) the On Error Resume Next statement and then run the program to see where an actual error occurs.
With the On Error Resume Next you never find out anything.
After having done so, please report the error and explain what is happening or is not happening.

Re: Export Excel to 2 tables Access ??????

It looks like there are a few issues.

1: As mentioned above do not use On Error Resume Next
2: You are using 2 connections, no need to use more than 1 as they are both pointed to the same database
3: Your are not using delimiters around your date value
4: It looks as though the value you are passing for date is not a valid date
5: Your first insert is trying to insert 8 fields but only looks to have 5 values

Re: Export Excel to 2 tables Access ??????

My Excel have just 5 value : MSSV,MaLop,TenLop,Ho,Ten,HanhKiem
My table Access have just 10 value : ID Number, MSSV,MaLop,Ho,Ten,HanhKiem,User,Status,Date.
I am trying to fix INSERT Statement but it is not still working.
Dim ex As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim i As Long: i = 1
Dim con As New ADODB.Connection
Dim con1 As New ADODB.Connection

First, I am stuck in Status Field in INSERT Statement because I want to use Find Statement .
If MSSV is found, the Status is shown Deactive
Else MSSV is not found, the Status is shown Active
Second, user and date in INSERT Statement does not working.

Re: Export Excel to 2 tables Access ??????

This would still apeear to not be a valid date

Code:

& "0" & Ngaygiodangnhap

Also Access uses # signs for date delimiters

I do not work with Excel so I do not know what those range() calls may be returning.
You should create a variable to hold your SQL Insert strings and then use debug.print to print out the content of the sql string once it has been built so you can see exactly what you are sending in your query. You could copy and paste the output string to a post so others can see what you are executing and maybe see the issue better.

Re: Export Excel to 2 tables Access ??????

Also remember that when you are using date fields in Access you must use # signs as delimiters where you are using ' instead.

Follow the advice in the post above and if you still have a problem, don;t tell us it is not working, tell us what it is doing or not doing and any messages that you receive. Not working could mean almost anything. You need to be clear as to what problems you experience in order to get accurate help.

I am trying to run the application, but the error I have got "Type mismatch". I think the problem is str() and I delete str(). But I have got the another error "Syntax error Insert Statement". How can you help me ????

Re: Export Excel to 2 tables Access ??????

Type mismatch most likely means that one or more of the fields you are working with is a numeric field in the database. You are passing the first 6 fields as text so if one of those is defined as numeric in the database you will get that error.

Did you do what was suggested in Post #10?

There may also be an issue with one of your field names Date is a reserved word and should not be used as a field name
If you do use such words as field names then you should put them in brackets to avoid issues