Posts by gollem

    Re: RUN-Time error 3001 , Application defined or object defined error at rs.Open

    I assume this is a typing error:

    Private Sub connect() 
        Dim rs As ADODB.RecordsetDim conn As New ADODB.Connection

    should be

    Private Sub connect() 
        Dim rs As ADODB.Recordset
        Dim conn As New ADODB.Connection cannot be used when using an insert sql-command.


    conn.execute strsql

    Re: Sql Query Based On Spreadsheet Cell

    Try this:

    "SELECT * FROM organization where organization.organizationid = " & Sheets("SQL").Range("b" & counter).Value  & " and organization.docclauseid = " & Sheets("SQL").Range("c" & counter).Value

    I think you forgot & " and organization.docclauseid

    Re: Convert Excel To PDF


    it depends on the software I think. My code is:

    Application.ActivePrinter = "PDFCreator on 00:"
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
            "PDFCreator on 00:", Collate:=True

    I use pdfcreator to convert. I have to confirm the conversion. Perhaps this method isn't good for you because you want to convert the file automatically I assume(without confirmation).

    Re: Convert Excel To PDF


    try to record a macro with the basic action.
    File-print-print to pdf-etc... Then test the macro, this should be your basic code to start.

    Re: VBA Code To Hide Sheets & Create Index Of Worksheets To Unhide


    I'm going to give you a start here, some examples.

    Here's a code that loops through all your sheets and hides all sheets where the name begins with "space". It also shows the use of a messagebox, displays sheetname.

    To select a sheet:


    To recognise if your sheet is a system or space I would you use the sheet name like "system_Basement" and "space_Basement" or you have to put a name on the sheet itself in a certain cell so every sheet has the same structure.

    Another tip: try to use the macro recorder and take a look at the code. Easy to learn that way.

    Hope this gives you a start.


    Re: Convert Excel To PDF


    according to me the code uses a "printer" to convert the file to a pdf.
    It's the same as on my computer. We can convert word, excel, ... to pdf by just using a printer "pdf" that converts and saves the file to a pdf-file.

    Unless you have this software active the code won't work. Check if you have in your printer list a pdfcreator or something like that.

    Re: Excel VBA and Oracle date formatin WHERE


    this is how I use it:

    strSQL = strSQL & " WHERE (LOCAL_TIMESTAMP>=TO_DATE('" & strStart & "', 'yyyy mm dd hh24:MI')"
    strSQL = strSQL & " AND LOCAL_TIMESTAMP<TO_DATE('" & strEnd & "', 'yyyy mm dd hh24:MI')"

    Try the TO_Date function from oracle with your date as string.
    Hope this helps.


    it depends on how you store the data in your database. Can you post your code?
    What do you mean by slow, slow saving for multiple records? Because saving 3 fields should be fast :) .
    I'm saving thousands of records with multiple columns in a few seconds.

    Hi Sal,

    some first things to check:
    - Access-database is not unlimited, you can only store until apr. 2GB (access 2003).
    4.500.000 records is a lot of data.
    - What are you going to do with this data? Access is slower than a sql-server...

    If you update every day such amount of data, perhaps it's better to only update the records that are changed, added, ... if possible of course.
    If I do such large updates I use a programming tool visual basic, create an application that I run every day at a certain time.


    Re: Vba Button Export Data From Xls To Mdb

    You should use ADO for this.
    The example below shows how to add data to a database using ADO.

    Don't forget to set your reference, otherwise the code won't work.

    Hope this gives you an idea.

    Re: DTPicker Not Visible with ElseIf


    at first sight nothing is wrong with the code. I've tested your code and indeed I've got the same problem. Interesting problem :)

    I've checked a few things and this should solve the issue:
    (add me.repaint after your code and it should work)

    Apparently you have to do a repaint when you use this method.

    Hope this helps.

    Re: File Protection With Vba


    you first need to unprotect the workbook with VBa, then you simply set the sheets to xlSheetVisible.

    If somebody doesn't enable the macro, the workbook is automatic save. Don't forget to set a password on your Vba-code, otherwise somebody can make the sheets visible without providing a password.

    Re: Ado Versus Odbc Sql Syntax


    I've used it before and it works for me:

    cnn.Provider = "Microsoft.jet.oledb.4.0"
    cnn.ConnectionString = "Data Source=c:\tmp\test.xls;Extended Properties=Excel 8.0;"

    Did you add the extended properties?

    Re: Error 429: Activex Component Can't Create Object


    normally you can use methods, you've combined two methods.

    1)This option needs the reference to the library

    Dim DTK As DTK 
    Set DTK = new DTK.application 'Something like this

    2)This option doesn't need the reference to the library

    Dim DTK as Object
    Set DTK = createObject("PureEdge.DTK")

    Hope this helps.

    Re: Filter Recordset With Parameter

    Small remark:

    if the field of your database is a text-field you have to add quotes:

    stParam = " WHERE [Intervention] = [COLOR="Red"]'[/COLOR]" & Me.Interventionlbl.Text  & "[COLOR="red"]'[/COLOR]"