Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16

    Angry Unanswered: ADOX issues Pleaee HELP!! urgent

    I have played around a bit with the ADOX in VB, and have been apble to get some code to work eirrectly from Visual Basic 6, However, THe aim of this was to get an import wizard working for ACCESS 2000... It almost seems to work, except when it comes to .additem in a combobox (with values generated from a Recordset)....

    Basically (excuse the puh:P) Here is the code thus far (that I have copied and pasted into a form in access, and given the controls the appropriate names).. Can someone PLEASE edit this to be ACCESS 2000 ready? As I thought it would work (seeing as how Access uses VBA!??)


    '****Following Placed in a Module*****
    Public cnnExcel As New ADODB.Connection
    Public rsExcel As New ADODB.Recordset

    'the following two lines dont seem to work in access
    'but are fine in Visual Basic???
    Public dbCreateTable As New ADOX.Catalog
    Public dbTablename As New ADOX.Table

    Public EFile, xlFile, dbTablename1
    Public objconn As New ADODB.Connection
    Public dbRecordsource As New ADODB.Recordset
    Public rsT As New ADODB.Recordset
    Public dbType As String
    '*** End Of Module *****

    '******START Of FOrm CODE

    Private Sub cmdExcel_Click()
    CommonDialog1.Filter = "MS Excel Files(*.xls)|*.xls|All Files(*.*)|*.*"
    CommonDialog1.ShowOpen
    EFile = CommonDialog1.FileName
    Me.ExcelFile.SetFocus
    Me.ExcelFile.Text = EFile
    Call xlConn
    Set rsExcel = cnnExcel.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
    Do Until rsExcel.EOF
    'the ".Additem" doesnt work?? Have tried as combo and
    'listboxes
    Me.xlCmb.AddItem rsExcel("TABLE_NAME")
    rsExcel.MoveNext
    Loop
    End Sub

    Private Sub Combo1_Click()
    dbType = Combo1.Text
    End Sub

    Private Sub Command1_Click()
    If UCase(dbType) = "MS ACCESS DATABASE" Then
    Form2.Show
    Else
    MsgBox "MS SQL will soon be supported by this program"
    End If
    End Sub

    Public Sub xlConn()
    With cnnExcel
    .Provider = "Microsoft.Jet.OLEDB.4.0;"
    .ConnectionString = "Data Source=" & EFile & "; Extended Properties=Excel 8.0;"
    .CursorLocation = adUseClient
    .Open
    End With
    End Sub

    Private Sub Form_Unload(Cancel As Integer)
    DoCmd.Close

    End Sub

    Private Sub xlCmb_Click()
    If ExcelFile.Text <> "" Then
    xlFile = xlCmb.Text
    Else
    MsgBox "Select the excel table"
    End If
    End Sub

    '******END CODE

    What I eventually hope to achieve here is a wizard (via about 3 forms) that will allow updates via Excel files (supplier pricelists) into various tables. The wizard will match up the worksheet names (Which will appear in the xlcmb listbox from the code above), with tablenames from the existing Database (YET TO WORK THAT BIT OUT!! :P), and then update the table(s) from the worksheet. I have a better understanding of VB than I do of Access, so am hoping that once I get a conversion of the code above, I can work out the rest. I am also almost illiterate as to ADO and ADOX, but I found some infor on the net that helped me put the above code together.

    Many thanks all, and PLEASE HURRY???
    Last edited by KiwiMarty; 10-28-03 at 05:01.
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: ADOX issues Pleaee HELP!! urgent

    Originally posted by KiwiMarty
    I have played around a bit with the ADOX in VB, and have been apble to get some code to work eirrectly from Visual Basic 6, However, THe aim of this was to get an import wizard working for ACCESS 2000... It almost seems to work, except when it comes to .additem in a combobox (with values generated from a Recordset)....

    Basically (excuse the puh:P) Here is the code thus far (that I have copied and pasted into a form in access, and given the controls the appropriate names).. Can someone PLEASE edit this to be ACCESS 2000 ready? As I thought it would work (seeing as how Access uses VBA!??)


    '****Following Placed in a Module*****
    Public cnnExcel As New ADODB.Connection
    Public rsExcel As New ADODB.Recordset

    'the following two lines dont seem to work in access
    'but are fine in Visual Basic???
    Public dbCreateTable As New ADOX.Catalog
    Public dbTablename As New ADOX.Table

    Public EFile, xlFile, dbTablename1
    Public objconn As New ADODB.Connection
    Public dbRecordsource As New ADODB.Recordset
    Public rsT As New ADODB.Recordset
    Public dbType As String
    '*** End Of Module *****

    '******START Of FOrm CODE

    Private Sub cmdExcel_Click()
    CommonDialog1.Filter = "MS Excel Files(*.xls)|*.xls|All Files(*.*)|*.*"
    CommonDialog1.ShowOpen
    EFile = CommonDialog1.FileName
    Me.ExcelFile.SetFocus
    Me.ExcelFile.Text = EFile
    Call xlConn
    Set rsExcel = cnnExcel.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "TABLE"))
    Do Until rsExcel.EOF
    'the ".Additem" doesnt work?? Have tried as combo and
    'listboxes
    Me.xlCmb.AddItem rsExcel("TABLE_NAME")
    rsExcel.MoveNext
    Loop
    End Sub

    Private Sub Combo1_Click()
    dbType = Combo1.Text
    End Sub

    Private Sub Command1_Click()
    If UCase(dbType) = "MS ACCESS DATABASE" Then
    Form2.Show
    Else
    MsgBox "MS SQL will soon be supported by this program"
    End If
    End Sub

    Public Sub xlConn()
    With cnnExcel
    .Provider = "Microsoft.Jet.OLEDB.4.0;"
    .ConnectionString = "Data Source=" & EFile & "; Extended Properties=Excel 8.0;"
    .CursorLocation = adUseClient
    .Open
    End With
    End Sub

    Private Sub Form_Unload(Cancel As Integer)
    DoCmd.Close

    End Sub

    Private Sub xlCmb_Click()
    If ExcelFile.Text <> "" Then
    xlFile = xlCmb.Text
    Else
    MsgBox "Select the excel table"
    End If
    End Sub

    '******END CODE

    What I eventually hope to achieve here is a wizard (via about 3 forms) that will allow updates via Excel files (supplier pricelists) into various tables. The wizard will match up the worksheet names (Which will appear in the xlcmb listbox from the code above), with tablenames from the existing Database (YET TO WORK THAT BIT OUT!! :P), and then update the table(s) from the worksheet. I have a better understanding of VB than I do of Access, so am hoping that once I get a conversion of the code above, I can work out the rest. I am also almost illiterate as to ADO and ADOX, but I found some infor on the net that helped me put the above code together.

    Many thanks all, and PLEASE HURRY???
    Do you have a reference set to the Microsoft ADO Ext 2. ? for DDL and Security?

    Gregg

  3. #3
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16
    I have a reference to the Microsoft ActiveX Data Object Libarary, but
    not sure about the DDL and Security?

    Sorry, but like i said, I am a bit on the ADOX illiterate Side
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by KiwiMarty
    I have a reference to the Microsoft ActiveX Data Object Libarary, but
    not sure about the DDL and Security?

    Sorry, but like i said, I am a bit on the ADOX illiterate Side
    Definitely check your references then. The ADOX reference is a separate reference then the ActiveX Data Object one.

    Gregg

  5. #5
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16
    Ahh.. that would be why I get "Data Method or Type Not Defined" errors.. What is the ADOX reference called then? I have every version of the ActiveX Data Objects Libraries (From 2.0 to 2.8) and have no idea what I am looking for for the ADOX reference!

    Opps, FOUND IT!! But I am still getting the error "Method or Data Type Not defined" for the .additem (xlcmb listbox)?
    Last edited by KiwiMarty; 10-29-03 at 00:18.
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by KiwiMarty
    Ahh.. that would be why I get "Data Method or Type Not Defined" errors.. What is the ADOX reference called then? I have every version of the ActiveX Data Objects Libraries (From 2.0 to 2.8) and have no idea what I am looking for for the ADOX reference!

    Opps, FOUND IT!! But I am still getting the error "Method or Data Type Not defined" for the .additem (xlcmb listbox)?
    The DDL and Security reference is the ADOX reference.

    By the way, do you have a reference set to the Access Object Library, in my case Microsoft Access 10.0? This is the one that will give you programmatic control over combo and list boxes.

    Gregg
    Last edited by basicmek; 10-29-03 at 07:59.

  7. #7
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16
    Yes, the Microsoft Access 9.0 object library was already referenced, but I STILL cant add the items to the ComboBox? Do I have to create another Recordset using ADO from the ADOX recordset (found something on the Microsoft MSDN pages that said combo and list boxes cannot have a source linked to the ADOX recordset?)

    If this IS the case, how do I create a recordset using ADO, that is generated from the ADOX recordset?

    Cheers
    KiwiMarty
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

  8. #8
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by KiwiMarty
    Yes, the Microsoft Access 9.0 object library was already referenced, but I STILL cant add the items to the ComboBox? Do I have to create another Recordset using ADO from the ADOX recordset (found something on the Microsoft MSDN pages that said combo and list boxes cannot have a source linked to the ADOX recordset?)

    If this IS the case, how do I create a recordset using ADO, that is generated from the ADOX recordset?

    Cheers
    KiwiMarty
    Do you have the RowSourceType of the combo or listbox set to value list? It looks like you are adding items manually with the additem method and this requires a value list setting.

    If this is the case, I don't see where the type of recordset you are using would affect the combo or list boxes.

    Gregg

  9. #9
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16
    DOH!! Heck, I shoulda seen that!! Thaks mate.. That was the issue for sure!! LOL (ohh, and the fact that I did not have the darn ADOX properly registered). Cheers heaps for your help on this one.

    I have decided to take a different route to the wizard, and this is alll valuable info! At least now I have combo and textboxes that are updated via the recordstes.

    Ohh.. anyone know if you can combine both textbox AND dropdown (as in combo boxes) in a flexgrid? Putting 200 odd on these on a form is a LONG process!! :P

    Cheers again all
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •