access[Resolved]

[MassImmune]

How do I Select a table in a database that has been loaded and stuff...I have to take an unknown ammount of fields and put them in a database.

[RobDog888]

Can you clarify what your trying to do a little more?

If you have records in a table then wouldnt the fields be known and arent they already in a db?

Same db or a different one?

[MassImmune]

No...They are coming from an excel spreadsheet.

[RobDog888]

So your records are in Excel and you need to copy them to an Access table.

What versions are you running?

[MassImmune]

It's Office 2000.

[RobDog888]

Ok, lets setup the connection to Access and Excel.

I am assuming that you do not need to display Access?

Option Explicit
'Add a reference to MS ActiveX Data Object 2.x Library
'Add a reference to MS Excel xx.0 Object Library (9.0 in your case)
Private moCnn As ADODB.Connection
Private moApp As Excel.Application
 
Private Sub Form_Load()
    Set moCnn = New ADODB.Connection
    moCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RobDog888.mdb;User Id=admin;Password=;"
    moCnn.Open
    Set moApp = New Excel.Application
    moApp.Visible = True
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    If TypeName(moCnn) <> "Nothing" Then
        If moCnn.State = adStateOpen Then moCnn.Close
    End If
    Set moCnn = Nothing
    If TypeName(moApp) <> "Nothing" Then
        moApp.Quit
    End If
    Set moApp = Nothing
End Sub

[MassImmune]

This is some of what I have so far

Private Sub cmdBrowse1_Click()
    On Error GoTo MyError
 
    With dlgOpen
        .CancelError = True
        .Flags = cdlOFNFileMustExist Or cdlOFNHideReadOnly Or cdlOFNPathMustExist
        .Filter = "Excel File (*.xls)|*.xls" 'Filters only for txt files
        .ShowOpen 'Open it
        FileName = dlgOpen.FileName  'Assign the filename to the FileName variable.
        txtExcelFile.Text = FileName ' Display the filename in a text box
    End With 'End with
    Exit Sub 'Exit sub
MyError:
    If Err.Number <> cdlCancel Then
        MsgBox Err.Number & " - " & Err.Description
    End If ' End If
End Sub ' End Sub
 
Private Sub cmdBrowse2_Click()
    On Error GoTo MyError
 
    With dlgOpen
        .CancelError = True 'Set the cancelError to True
        .Flags = cdlOFNFileMustExist Or cdlOFNHideReadOnly Or cdlOFNPathMustExist
        .Filter = "Access File (*.mdb)|*.mdb" 'Filters only for txt files
        .ShowOpen 'Open it
        DBFileName = dlgOpen.FileName    'Assign the filename to the FileName variable
        txtAccessFile.Text = DBFileName  'Display the filename in a text box
    End With 'End with
    Exit Sub 'Exit sub
MyError:
    If Err.Number <> cdlCancel Then
        MsgBox Err.Number & " - " & Err.Description
    End If 'End If
End Sub ' End Sub
 
Private Sub cmdLoad_Click(Index As Integer)
 
If FileName = "" Then
    Dim Response As Integer     'Declare Response variable
    Response = MsgBox("Please open an excel file.", vbOKOnly, "Error") 'Dipsplay error msg.
Else
    Dim oWB As Excel.Workbook   'Declare oWB as a new excel workbook
 
    moApp.Visible = True
    Set oWB = moApp.Workbooks.Open(FileName)    'Set oWD as the file name selected previously
    RowCount = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row 'Get the number of rows
    ColumnCount = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column 'Get the number of columns
    txtColumns.Text = ColumnCount 'Display the number of columns
    txtrows.Text = RowCount 'Display the number of rows
    frmExceltoOracle.Show
    oWB.Close False     'Set the oWB close to false
    Set oWB = Nothing   'Set the oWb to nothing
    moApp.Visible = False   'Set the visibility of the MoApp to false
End If  'End If
MsgBox "Excel File Loaded Properly" 'Proves that the Database connected properly.
End Sub 'End sub
 
 
Private Sub cmdLoad1_Click()
If DBFileName = "" Then
    Dim Response As Integer     'Declare Response variable
    Response = MsgBox("Please open an Access file.", vbOKOnly, "Error") 'Display error msg
Else
    Dim adoMyConnection As ADODB.Connection     'Declare The ADO connection
 
    Set adoMyConnection = New ADODB.Connection  'Set the adoMyConnection as a new ADO connection
    With adoMyConnection
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dlgOpen.FileName & ";Persist Security Info=False" 'Set your connection options
        .Open 'Attempt to connect
        MsgBox "Access File loaded Properly" 'Proves that the Database connected properly.
    End With
End If  'End If
End Sub 'End sub
 
Private Sub Form_Load()
    DBFileName = "" 'Set the database file name to nothing
    FileName = ""   'Set the access file name to nothing
    Set moApp = New Excel.Application   'Set moApp as a new excel application
End Sub 'End Sub

[RobDog888]

Just a few pointers to avoid the never terminating excel instance.

RowCount = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row 'Get the number of rows
'Should be:
RowCount = oWB.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row 'Get the number of rows
'oWB.Sheets(?) where ? is the sheet you need your data from

[MassImmune]

But now my biggest problem is taking a row from the excel spreadsheet and putting it into the database. Is it even possible the way I'm doing it by reading each field in the spreadsheet at a time.

[RobDog888]

Do you have allot of rows in Excel? If so then it would be better to use ADO to connect to the sheet
and insert it into access.

[MassImmune]

The number of rows will never be determined...It depends on the set up that the client has going.

[RobDog888]

Ok, I'll post an example of connecting to Excel using ADO. After you query the sheet all you do is loop
through the recordset executing an insert statement of the current record. The only problem will be to alter
the table to match the sheet on the fly.

[RobDog888]

Here is a sample I wrote that will get you going. All you need to do is the dynamic table alterations
depending on excels used columns.

Option Explicit
'Add a reference to MS ActiveX Data Objects 2.x Library
'Add a command button (Command1)
Private moCnn As ADODB.Connection
 
Private Sub Command1_Click()
 
    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
    
    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)
    Set rsXl = New ADODB.Recordset
    rsXl.Open "SELECT * FROM '" & rsSchema.Fields("TABLE_NAME").Value & "'", cnnXls, adOpenStatic, adLockReadOnly, adCmdText
    rsSchema.Close
    Set rsSchema = Nothing
    
    iCount = rsXl.RecordCount
    iCols = rsXl.Fields.Count
    
    If rsXl.BOF = True And rsXl.EOF = True Then
        MsgBox "No Excel data found"
    Else
        For i = 1 To rsXl.RecordCount
            moCnn.Execute "INSERT INTO Table1 VALUES ('" & rsXl.Fields(0).Value & "', '" & rsXl.Fields(1).Value & "')", lRecs
            rsXl.MoveNext
        Next
    End If
    
    If rsXl.State = adStateOpen Then rsXl.Close
    Set rsXl = Nothing
    If cnnXls.State = adStateOpen Then cnnXls.Close
    Set cnnXls = Nothing
    
End Sub
 
Private Sub Form_Load()
    Set moCnn = New ADODB.Connection
    moCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RobDog888.mdb;User Id=admin;Password=;"
    moCnn.Open
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    If TypeName(moCnn) <> "Nothing" Then
        If moCnn.State = adStateOpen Then moCnn.Close
    End If
    Set moCnn = Nothing
End Sub

[MassImmune]

It's probably asking to much but is there a way that you can break it up into sections so I know exactly whats going on and what each bit of code is doing...I'm just a noobie remember...

[RobDog888]

No problem at all. Actually it nice to hear that someone actually want to understand and learn from the
code instead of the usual copy and paste code and poof - I'm a programmer.

'This first line set a requirement that all variables are declared
Option Explicit
'Then we add a reference to ADO (for early binding)
'Add a reference to MS ActiveX Data Objects 2.x Library
'Add a command button (Command1)
'Decalre a module level var connection object to use to connect to Access.
Private moCnn As ADODB.Connection
 
Private Sub Command1_Click()
    'Declare a connection object to connect to excel sheet.
    Dim cnnXls As ADODB.Connection
    'Declare a recordset object to get the structure of the workbook- sheet names, etc
    Dim rsSchema As ADODB.Recordset
    'Declare a rs for reading the data off of the sheet
    Dim rsXl As ADODB.Recordset
    Dim lRecs As Long
    Dim iCount As Integer
    Dim iCols As Integer
    Dim i As Integer
    
    'start a new instance of the excel connection
    Set cnnXls = New ADODB.Connection
    'Open the connection
    cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
    'set a rs to get the workbook structure.
    Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
    'set a new rs that will read the excel data
    Set rsXl = New ADODB.Recordset
    'open/retrieve data on the sheet passed in rsSchema (first sheet since we are not searching for a specific sheet. 
    'This can be added if its a specific sheet thats not the first one).
    'rsSchema.Fields("TABLE_NAME").Value = "Sheet1" - first sheet or whatever the first sheet it named.
    rsXl.Open "SELECT * FROM '" & rsSchema.Fields("TABLE_NAME").Value & "'", cnnXls, adOpenStatic, adLockReadOnly, adCmdText
    'clean up
    rsSchema.Close
    Set rsSchema = Nothing
    
    iCount = rsXl.RecordCount
    iCols = rsXl.Fields.Count
    'test for returned data in the rs
    If rsXl.BOF = True And rsXl.EOF = True Then
        MsgBox "No Excel data found"
    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
        For i = 1 To rsXl.RecordCount
            moCnn.Execute "INSERT INTO Table1 VALUES ('" & rsXl.Fields(0).Value & "', '" & rsXl.Fields(1).Value & "')", lRecs
            rsXl.MoveNext
        Next
    End If
    'clean up the recordset and connection
    If rsXl.State = adStateOpen Then rsXl.Close
    Set rsXl = Nothing
    If cnnXls.State = adStateOpen Then cnnXls.Close
    Set cnnXls = Nothing
    
End Sub
 
Private Sub Form_Load()
    'start a new connection to Access
    Set moCnn = New ADODB.Connection
    moCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\RobDog888.mdb;User Id=admin;Password=;"
    moCnn.Open
End Sub
 
Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    'clean up/close the connection to access
    If TypeName(moCnn) <> "Nothing" Then
        If moCnn.State = adStateOpen Then moCnn.Close
    End If
    Set moCnn = Nothing
End Sub

[MassImmune]

rsXl.Open "SELECT * FROM '" & rsSchema.Fields("TABLE_NAME").Value & "'", cnnXls, adOpenStatic, adLockReadOnly, adCmdText

Everytime I run this line it comes back with an error that says: Syntax error in query. Incomplete query clause.

I'm not sure what it means or if I'm supposed to put something in there or not.

[RobDog888]

After the rsSchema is opened add this line and place a breakpoint on it.

Debug.Print rsSchema.Fields("TABLE_NAME").Value

Whats the value?

[MassImmune]

Nothing happened...unless I put it in the wrong spot...and I'm not sure what you mean by values.

[RobDog888]

Before this line:

rsXl.Open "SELECT * FROM '" & rsSchema.Fields("TABLE_NAME").Value & "'", cnnXls, adOpenStatic, adLockReadOnly, adCmdText

Place the debug.print code I previously posted and view the results in the immediate window.

So it should look like this...

Debug.Print rsSchema.Fields("TABLE_NAME").Value 
rsXl.Open "SELECT * FROM '" & rsSchema.Fields("TABLE_NAME").Value & "'", cnnXls, adOpenStatic, adLockReadOnly, adCmdText

[MassImmune]

It still came up with the same error as before.

[RobDog888]

But what does this display in the immediate window. This is important to determine what the error is
comming from. Also try stepping through the code to see what line.

Debug.Print rsSchema.Fields("TABLE_NAME").Value

[MassImmune]

Is this what you mean?

[RobDog888]

So Sheet1$ is your sheet name being retrieved by the rsSchema recordset.
The error may be coming from the $ sign. Let me test something.

[RobDog888]

Ok, found the issue. Seems the sheet name needs to be wrapped around with the "`" character. Its the key
just to the left of the #1 key and above the Tab key. "`" = ASCII 96.

Private Sub Command1_Click()
    'Declare a connection object to connect to excel sheet.
    Dim cnnXls As ADODB.Connection
    'Declare a recordset object to get the structure of the workbook- sheet names, etc
    Dim rsSchema As ADODB.Recordset
    'Declare a rs for reading the data off of the sheet
    Dim rsXl As ADODB.Recordset
    Dim lRecs As Long
    Dim iCount As Integer
    Dim iCols As Integer
    Dim i As Integer
    
    'start a new instance of the excel connection
    Set cnnXls = New ADODB.Connection
    'Open the connection
    cnnXls.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;"
    'set a rs to get the workbook structure.
    Set rsSchema = cnnXls.OpenSchema(adSchemaTables)
    'set a new rs that will read the excel data
    Set rsXl = New ADODB.Recordset
    'open/retrieve data on the sheet passed in rsSchema (first sheet since we are not searching for a specific sheet.
    'This can be added if its a specific sheet thats not the first one).
    'rsSchema.Fields("TABLE_NAME").Value = "Sheet1" - first sheet or whatever the first sheet it named.
    Debug.Print rsSchema.Fields("TABLE_NAME").Value
    'Filter the rs so its on the record (sheet name) we need.
    rsSchema.Filter = "TABLE_NAME = 'Sheet1$'" '<-- CHANGE TO THE DESIRED SHEET NAME
    If rsSchema.BOF = True And rsSchema.EOF = True Then
        MsgBox "Excel sheet not found!", vbOKOnly + vbExclamation
        GoTo CleanUp
    End If
    rsXl.Open "SELECT * FROM `" & rsSchema.Fields("TABLE_NAME").Value & "`", cnnXls, adOpenStatic, adLockReadOnly, adCmdText
    'clean up
    rsSchema.Close
    Set rsSchema = Nothing
    
    iCount = rsXl.RecordCount
    iCols = rsXl.Fields.Count
    'test for returned data in the rs
    If rsXl.BOF = True And rsXl.EOF = True Then
        MsgBox "No Excel data found"
    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
        For i = 1 To rsXl.RecordCount
            moCnn.Execute "INSERT INTO Table1 VALUES ('" & rsXl.Fields(0).Value & "', '" & rsXl.Fields(1).Value & "')", lRecs
            rsXl.MoveNext
        Next
    End If
CleanUp:
    'clean up the recordset and connection
    If rsXl.State = adStateOpen Then rsXl.Close
    Set rsXl = Nothing
    If cnnXls.State = adStateOpen Then cnnXls.Close
    Set cnnXls = Nothing
End Sub

[MassImmune]

That solved that problem......but now it says that the number of query values and destination fields are not the same...

[RobDog888]

So then that means that you number of columns in Excel does not match the number of feilds in your Access table.

[MassImmune]

I noticed I had the wrong excel file selected but when I use the new one it still says that the number of query values and destination fields are not the same.

[RobDog888]

How many fields in your access table are there and how many used columns (max) are there in your
sheet? They need to match.

[MassImmune]

I have 4 fields in the database and I have 4 columns in excel...So I don't see why it won't work for me.

[RobDog888]

Step through the code and check the value for rsXl.Fields.Count to make sure it is getting the correct column count.
Then make sure your field types match the data types in your column in Excel.

[MassImmune]

Yeah...After both the iColumn and iCols count lines I added a messege box that shows the value of the 2 variables and they both say 4.

[RobDog888]

iColumn ? Could you post your updated code?

[MassImmune]

Typo...I meant to put iCount...sorry about that.

[RobDog888]

Oh, iCount is the record or row count. Not the field count.
iCols = rsXl.Fields.Count is the column count in Excel.

[MassImmune]

Are those to values supposed to be equal...After I run it and the error comes up I click debug and it highlights this line so maybe it's something wrong with it:

moCnn.Execute "INSERT INTO Table1 VALUES ('" & rsXl.Fields(0).Value & "', '" & rsXl.Fields(1).Value & "')", lRecs

[RobDog888]

Ok, in Access, your table consists of what fields? Also that line needs to be modified to match your number
of fields and column.

rsXl.Fields(0).Value, 1, 2, ... etc matching the column in excel and the feilds in access

[MassImmune]

The 4 fields are Name, Age, Birthdate(just the year) and income...

[RobDog888]

Replace the line with this.

moCnn.Execute "INSERT INTO Table1 VALUES ('" & rsXl.Fields(0).Value & "', '" & rsXl.Fields(1).Value & "', '" & rsXl.Fields(2).Value & "', '" & rsXl.Fields(3).Value & "')", lRecs

What you also need to watch the field data types. If you have a field as Numeric then you dont need the single quotes are around that particular field, only text fields.

[MassImmune]

For some reason it isn't taking the first row in the excel file. It works but when I check the database, The first row from the excel file is missing.

[RobDog888]

How about doing a rsXl.MoveFirst befor the insert loop?