access[Resolved]

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.

I was just realizing that I took it the wrong way. Since it is already being loaded with the schema,
you can load the sheets in a cbo and that should be it except for inserting the selection in the rsXL open query
as a criteria for only getting the selected sheet.

And how do I do that?... :ehh:...I don't really understand

Instead of "filtering" the rsSchema to just one sheet, you loop and add each rsSchema record (sheet name) to the cbo.
Then when they click the command button you take the selection in the cbo aand pass it in your query string
to open the rsXL recordset.

What are the commands to get the sheet names and to put them in the cbo...Also...If the "filter" is a part of the import button, then the user wouldn't have time to select what sheet they wanted...

This is the related code to place in the browse button procedure. Note you will have duplicate
code in the import procedure and this code is just for logic and not functionality.

VB Code:

Set cnnXls = New ADODB.Connection
 
cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
 
Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
If rsSchema.BOF = True And rsSchema.EOF = True Then
    MsgBox "Excel sheet not found!", vbOKOnly + vbExclamation
    GoTo CleanUp
Else
    Dim i as integer
    Combo1.Clear
    For i = 1 to rsSchema.RecordCount
        Combo1.AddItem rsSchema.Fields(0).Value
    Next
End If
'Blah blah blah....

The code runs smoothly when I put it in and fixed it up a bit to work with my program but the following code doesn't seem to be writing anything to the combo box.:

VB Code:

Combo1.AddItem rsSchema.Fields(0).Value

Did you debug.print rsSchema.Fields(0).Value to verify there is a value in the rs?

Yeah I just did it and it says Null.

Well thats an issue of the recordset not containing any data for the first record?
I also noticed that I missed a line of code so I will update the logic I previously posted.

VB Code:

Set cnnXls = New ADODB.Connection
 
cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
 
Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
If rsSchema.BOF = True And rsSchema.EOF = True Then
    MsgBox "Excel sheet not found!", vbOKOnly + vbExclamation
    GoTo CleanUp
Else
    Dim i as integer
    Combo1.Clear
    For i = 1 to rsSchema.RecordCount
        Combo1.AddItem rsSchema.Fields(0).Value
        rsSchem.MoveNext '<-- Added this line to iterate through the rs.
    Next
End If
'Blah blah blah....

I don't really see where this takes the sheet names and puts them in the combo box :ehh:

Ok, i'll walk you through it.

VB Code:

'Connect to Book1.xls
Set cnnXls = New ADODB.Connection
 
cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
 
'Open the schema of book1
Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
If rsSchema.BOF = True And rsSchema.EOF = True Then
    MsgBox "Excel sheet not found!", vbOKOnly + vbExclamation
    GoTo CleanUp
Else
    Dim i as integer
    Combo1.Clear
    For i = 1 to rsSchema.RecordCount
        'Add each sheet name to the combo
        Combo1.AddItem rsSchema.Fields(0).Value
        rsSchem.MoveNext '<-- Added this line to iterate through the rs.
    Next
End If
'Blah blah blah....

But what I don't understand is why it isn't working...

Post your code on this. Thats just it, its already working on verifing the sheet name exists when we
do a .Filter so it must be somethng else.

VB Code:

Private Sub cmdConnect_Click()
    'Declare Temperary Variables
    Dim cnnXls As ADODB.Connection
    Dim rsSchema As ADODB.Recordset
    Dim rsXl As ADODB.Recordset
    Dim i As Integer
 
    'Set cnnXls to a new ADODB.Connection
    Set cnnXls = New ADODB.Connection
 
    If frmOpen.FileName1 = "" Then
        If FileName = "" Then
        'Display a message box telling the user to open an excel file.
        MsgBox "Please Open an Excel File"
        Else
            'Connect to the selected file
            cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=Excel 8.0;"
            'set the rsSchema to the cnnXls
            Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
            'Display the value in the rsSchema
            Debug.Print rsSchema.Fields(0).Value
            If rsSchema.BOF = True And rsSchema.EOF = True Then
                'Display a messege box telling the user that an Excel sheet wasn't found
                MsgBox "Excel sheet not found!", vbOKOnly + vbExclamation
                'Go to the Clean up function
                GoTo CleanUp
            Else
            
            'Clear the Combo box
            Combo1.Clear
                For i = 1 To rsSchema.RecordCount
                    'Add the current value of the schema to the Combo box
                    Combo1.AddItem rsSchema.Fields(0).Value
                    'Move the schema to the next one
                    rsSchema.MoveNext
                Next
                MsgBox "Connected Successfully"
            End If  'End If
        End If  'End If
    Else
        'Connect to the selected file
        cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & frmOpen.FileName1 & ";Extended Properties=Excel 8.0;"
        'set the rsSchema to the cnnXls
        Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
        'Display the value in the rsSchema
        Debug.Print rsSchema.Fields(0).Value
        If rsSchema.BOF = True And rsSchema.EOF = True Then
            'Display a messege box telling the user that an Excel sheet wasn't found
            MsgBox "Excel sheet not found!", vbOKOnly + vbExclamation
            'Go to the Clean Up function
            GoTo CleanUp
        Else
        
            'Clear the Combo box
            Combo1.Clear
                For i = 1 To rsSchema.RecordCount
                    'Add the current value of the schema to the Combo box
                    Combo1.AddItem rsSchema.Fields(0).Value
                    'Move the schema to the next one
                    rsSchema.MoveNext
                Next
                MsgBox "Connected Successfully"
        End If  'End If
    End If  'End If
 
'Clean Up Function
CleanUp:
    'Set the recordset to nothing
    Set rsXl = Nothing
    If cnnXls.State = adStateOpen Then cnnXls.Close
End Sub     'End Sub

Do any of the rsSchema records contain data? Step through the code to see where or what is going wrong.
What is the error message, if any?

The excel file has 3 sheets all containing the same data...There is no error actually...A message box pops up saying it connected successfully but when I check the combo box, theres nothing there.

Place a break point on the line - For i = 1 To rsSchema.RecordCount
and step through the addition of the sheets
to the cbo. Make sure you dont have any other code that is .Clear 'ing the cbo somewhere else.

The only spot that is using that specific cbo is that button...:(

So as you step through the For loop, every record is Null?

Yes...That is correct

But we had it working. The code is the same, so something must have changed? Different workbook?

Everything works fine...it still imports...i haven't changed any of that yet...not till I get the cbo working. It never did put the names of the sheets in the cbo.

But the code is the same from the importing procedure. So if it works in there then it should work in your new procedure.
At the end of the import procedure are you closing the connection and destroying the objects?

I click open....select the file I want....then I click connenct...that should connect to the file and put the sheets into the cbo....the import comes after they have selected what sheet they want.

My point was that from before the new code addition, the import worked fine.
You need to debug your project to determine where and why the rsSchema is returning multiple Null records.

I discovered that for some reason its not going into the for statement. Probably because the rschema is null...but I can't figure our why it is. I have no clue.

What is the value of - rsSchema.RecordCount?

Umm...-1 :ehh:

The recordcount may not be supported with OpenSchema.
Change your For loop to a Do While loop.

VB Code:

'Ex.
Do While rsSchema.EOF = False
    cbo.additem rsSchema.fields(0).value
    rsSchema.movenext
Loop

Error saying Invalid use of Null.

I dont know what to tell you, but if the original import procedure is running then this one should too? There must
be a logical reason for it.

I was just going back over the thread to see the original code and this is how the field should be referenced.

VB Code:

rsSchema.Fields("TABLE_NAME").Value

I have no idea either...I looked at the code for both the connect button and the import button....and I don't see any difference...which really confuses me. I got the access cbo working kinda....it has the System tables in it or something and I'm not sure how to get rid of them or w/e...

System tables, you mean the Access system tables? Can you post the two procedures of code?

VB Code:

Private Sub cmdConnect2_Click()
    'Declare Temporary variables
    Dim Cnxn As ADODB.Connection
    Dim rstSchema As ADODB.Recordset
    Dim strCnxn As String
    
    If frmOpen.DBFileName1 = "" Then
        If DBFileName = "" Then
            'Display a message box telling the user to open an access file.
            MsgBox "Please Open an Access File"
        Else
            'Set Cnxn as a new ADODB connection
            Set Cnxn = New ADODB.Connection
            'Connect to the selected file
            Cnxn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & DBFileName & ";" & "Persist Security Info=False"
            'Open it
            Cnxn.Open strCnxn
            'Set the rstschema equal to Cnxn.OpenSchema(adSchemaTables)
            Set rstSchema = Cnxn.OpenSchema(adSchemaTables)
   
            Do Until rstSchema.EOF
                'Add the table name to the combo box
                Combo2.AddItem rstSchema!TABLE_NAME
                'move to the next one
                rstSchema.MoveNext
            Loop
   
            'Close the schema
            rstSchema.Close
            'Close the excel file
            Cnxn.Close
            'Set the schema to nothing
            Set rstSchema = Nothing
            'Set the Cnxn to nothing
            Set Cnxn = Nothing
            'Display a message box saying that it was connected properly
            MsgBox "Connected Properly"
        End If  'End If
    Else
        'Set Cnxn as a new ADODB connection
        Set Cnxn = New ADODB.Connection
        'Connect to the selected file
        Cnxn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & frmOpen.DBFileName1 & "" & "Persist Security Info=False"
        'Open it
        Cnxn.Open strCnxn
      
        'Set the rstschema equal to Cnxn.OpenSchema(adSchemaTables)
        Set rstSchema = Cnxn.OpenSchema(adSchemaTables)
   
        Do Until rstSchema.EOF
            'Add the table name to the combo box
            Combo2.AddItem rstSchema!TABLE_NAME
            'move to the next one
            rstSchema.MoveNext
        Loop
   
        'Close the schema
        rstSchema.Close
        'Close the excel file
        Cnxn.Close
        'Set the schema to nothing
        Set rstSchema = Nothing
        'Set the Cnxn to nothing
        Set Cnxn = Nothing
        'Display a message box saying that it was connected properly
        MsgBox "Connected Properly"
    End If  'End If
End Sub     'End Sub

Ah, looks like your connecting to Access and not Excel. So your getting the schema of Access and not Excel.

Quote:

---

Originally Posted by MassImmune

I got the access cbo working kinda....it has the System tables in it or something and I'm not sure how to get rid of them or w/e...

---

Sorry about that...that was the code for the access cbo part...I'll put the other below.

VB Code:

Private Sub cmdConnect_Click()
    'Declare Temperary Variables
    Dim cnnXls As ADODB.Connection
    Dim rsSchema As ADODB.Recordset
    Dim rsXl As ADODB.Recordset
    Dim i As Integer
 
    'Set cnnXls to a new ADODB.Connection
    Set cnnXls = New ADODB.Connection
 
    If frmOpen.FileName1 = "" Then
        If FileName = "" Then
        'Display a message box telling the user to open an excel file.
        MsgBox "Please Open an Excel File"
        Else
            'Connect to the selected file
            cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=Excel 8.0;"
            'set the rsSchema to the cnnXls
            Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
            'Display the value in the rsSchema
            Counter = rsSchema.RecordCount
            MsgBox "" & Counter & ""
            
            Debug.Print rsSchema.Fields("TABLE_NAME").Value
            If rsSchema.BOF = True And rsSchema.EOF = True Then
                'Display a messege box telling the user that an Excel sheet wasn't found
                MsgBox "Excel sheet not found!", vbOKOnly + vbExclamation
                'Go to the Clean up function
                GoTo CleanUp
            Else
            
            'Clear the Combo box
            Combo1.Clear
                For i = 1 To rsSchema.RecordCount
                    'Add the current value of the schema to the Combo box
                    Combo1.AddItem rsSchema.Fields("TABLE_NAME").Value
                    'Move the schema to the next one
                    rsSchema.MoveNext
                Next
                MsgBox "Connected Successfully"
            End If  'End If
        End If  'End If
    Else
        'Connect to the selected file
        cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & frmOpen.FileName1 & ";Extended Properties=Excel 8.0;"
        'set the rsSchema to the cnnXls
        Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
        'Display the value in the rsSchema
        Debug.Print rsSchema.Fields("TABLE_NAME").Value
        If rsSchema.BOF = True And rsSchema.EOF = True Then
            'Display a messege box telling the user that an Excel sheet wasn't found
            MsgBox "Excel sheet not found!", vbOKOnly + vbExclamation
            'Go to the Clean Up function
            GoTo CleanUp
        Else
        
            'Clear the Combo box
            Combo1.Clear
                For i = 1 To rsSchema.RecordCount
                    'Add the current value of the schema to the Combo box
                    Combo1.AddItem rsSchema.Fields("TABLE_NAME").Value
                    'Move the schema to the next one
                    rsSchema.MoveNext
                Next
                MsgBox "Connected Successfully"
        End If  'End If
    End If  'End If
    
Exit Sub
'Clean Up Function
CleanUp:
    'Set the recordset to nothing
    Set rsXl = Nothing
    If cnnXls.State = adStateOpen Then cnnXls.Close
End Sub     'End Sub

And the Import:

VB Code:

Private Sub cmdImport_Click()
    'Declare temporary variables
    Dim cnnXls As ADODB.Connection
    Dim rsSchema As ADODB.Recordset
    Dim rsXl As ADODB.Recordset
    Dim lRecs As Long
    Dim iCount As Integer
    Dim iCols As Integer
    Dim i As Integer
    Dim sFields As String
    Dim iValues As Integer
    Dim sValues As String
    
    If FileName = "" Then
        If DBFileName = "" Then
            FileName = frmOpen.FileName1
            DBFileName = frmOpen.DBFileName1
        Else
        End If
    Else
    End If
    
    If FileName = "" Then
        If DBFileName = "" Then
            MsgBox "Please make sure you have the nescessry files open"
        Else
            'Set the ADODB connection as a new ADODB connection
            Set cnnXls = New ADODB.Connection
    
    
            'Open the selected Excel Project
            cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=Excel 8.0;"
    
            'Set the Record set equal to the cnnXls open schema
            Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
            'Set rsXl as a new record set
            Set rsXl = New ADODB.Recordset
    
            Debug.Print rsSchema.Fields("TABLE_NAME").Value
            
            'Filter the record set to get the table name
            rsSchema.Filter = "TABLE_NAME = 'Sheet1$'"
            If rsSchema.BOF = True And rsSchema.EOF = True Then
                'Disply a message box stating that a Excel file was not found
                MsgBox "Excel sheet not found!", vbOKOnly + vbExclamation
                'Go to the clean up section
                GoTo CleanUp
            End If 'End if
            rsXl.Open "SELECT * FROM `" & rsSchema.Fields("TABLE_NAME").Value & "`", cnnXls, adOpenStatic, adLockReadOnly, adCmdText
            
            'Close the record set
            rsSchema.Close
    
            'Set the record set to nothing
            Set rsSchema = Nothing
    
            'Get the number of records
            iCount = rsXl.RecordCount
            'Get the number of fields
            iCols = rsXl.Fields.Count
            
            If rsXl.BOF = True And rsXl.EOF = True Then
                'Disply a message box stating that a Excel data was not found
                MsgBox "No Excel data found"
            Else
                'Fill the sFields
                sFields = vbNullString
                For i = 0 To rsXl.Fields.Count - 1
                    sFields = sFields & " " & rsXl.Fields(i).Name & ","
                Next
                sFields = Left$(sFields, Len(sFields) - 1)
        
                'Delete all the records from
                moCnn.Execute "DELETE FROM Table1;", lRecs
                'Display a message box telling the user how many records were deleted
                MsgBox lRecs & " - Records deleted!", vbOKOnly + vbInformation
        
                For i = 1 To rsXl.RecordCount
                    'Assing a null string to the sValues variable
                    sValues = vbNullString
            
                    For iValues = 0 To rsXl.Fields.Count - 1
                        sValues = sValues & rsXl.Fields(iValues).Value & "','"
                    Next
                sValues = Left$(sValues, Len(sValues) - 2)
        
                'Insert the info into the database
             moCnn.Execute "INSERT INTO Table1 (" & sFields & ") VALUES ('" & sValues & ")", lRecs
        
                'Add 1 to the counter
                Counter = Counter + 1
        
                'Move the the next record
                rsXl.MoveNext
                Next
            End If  'End if
    
            'Display a message box telling the user how many records were imported
            MsgBox Counter & " -Records imported successfully!", vbOKOnly + vbInformation
            'Set the counter to 0
            Counter = 0
        End If
    Else
        'Set the ADODB connection as a new ADODB connection
        Set cnnXls = New ADODB.Connection
    
    
        'Open the selected Excel Project
        cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=Excel 8.0;"
    
        'Set the Record set equal to the cnnXls open schema
        Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
        'Set rsXl as a new record set
        Set rsXl = New ADODB.Recordset
    
        Debug.Print rsSchema.Fields("TABLE_NAME").Value
            
        'Filter the record set to get the table name
        rsSchema.Filter = "TABLE_NAME = 'Sheet1$'"
        If rsSchema.BOF = True And rsSchema.EOF = True Then
            'Disply a message box stating that a Excel file was not found
            MsgBox "Excel sheet not found!", vbOKOnly + vbExclamation
            'Go to the clean up section
            GoTo CleanUp
        End If 'End if
        rsXl.Open "SELECT * FROM `" & rsSchema.Fields("TABLE_NAME").Value & "`", cnnXls, adOpenStatic, adLockReadOnly, adCmdText
            
        'Close the record set
        rsSchema.Close
    
        'Set the record set to nothing
        Set rsSchema = Nothing
    
        'Get the number of records
        iCount = rsXl.RecordCount
        'Get the number of fields
        iCols = rsXl.Fields.Count
            
        If rsXl.BOF = True And rsXl.EOF = True Then
            'Disply a message box stating that a Excel data was not found
            MsgBox "No Excel data found"
        Else
            'Fill the sFields
            sFields = vbNullString
            For i = 0 To rsXl.Fields.Count - 1
                sFields = sFields & " " & rsXl.Fields(i).Name & ","
            Next
            sFields = Left$(sFields, Len(sFields) - 1)
              
            'Delete all the records from
            moCnn.Execute "DELETE FROM Table1;", lRecs
            'Display a message box telling the user how many records were deleted
            MsgBox lRecs & " - Records deleted!", vbOKOnly + vbInformation
    
            For i = 1 To rsXl.RecordCount
                'Assing a null string to the sValues variable
                sValues = vbNullString
            
                For iValues = 0 To rsXl.Fields.Count - 1
                    sValues = sValues & rsXl.Fields(iValues).Value & "','"
                Next
            sValues = Left$(sValues, Len(sValues) - 2)
        
            'Insert the info into the database
             moCnn.Execute "INSERT INTO Table1 (" & sFields & ") VALUES ('" & sValues & ")", lRecs
        
            'Add 1 to the counter
            Counter = Counter + 1
        
            'Move the the next record
            rsXl.MoveNext
            Next
        End If  'End if
    
        'Display a message box telling the user how many records were imported
        MsgBox Counter & " -Records imported successfully!", vbOKOnly + vbInformation
        'Set the counter to 0
        Counter = 0
    End If
Exit Sub
'Clean up
CleanUp:
    'Set the recordset to nothing
    Set rsXl = Nothing
    If cnnXls.State = adStateOpen Then cnnXls.Close
End Sub 'End sub

The only difference is that we are filtering for a specific sheet.
Try adding the filter code and see if it gets a sheet.

Nope, the recordset is still at -1