Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Invalid NZ function (Excel/Access/VBA 2k)

    Hello,
    first of all I am not sure in which forum to place this but as I am using Automation I opted for VBA forum. Please fell free to move it to wherever most appropriate.

    I use the following code snippet to import data as a result of "qryL_AllInfo" query into Excel. The query is saved as an object in my MIS.mdb user-level secured database.
    However, I get an error "invalid NZ function" in this line:
    <pre>.Open , , 3, 3</pre>


    I am running the code from Excel and I have references to Access, ADO and ADOX.
    I tried also early binding without any success.
    The syntax of the NZ functions is similar to this:

    NZ([L_pol])-NZ([L_d]) AS Diffl

    I also tried to change it into NZ([field],0) but it did not help.
    When I run the query from Access directly, it works without erroring out.

    here is the code:
    <pre>Sub ImportLB()
    Dim Cn As Object, sRs As Object, myCalls As String
    Dim mySql As String, dbFullname As String, myCnt As Long

    Workbooks.Add
    Set tgtSh = ActiveWorkbook.Sheets(1)
    mySql = "SELECT * FROM qryL_AllInfo;" 'Stack SQL string

    myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=CataAccess2000MISMIS.mdb;" & _
    "Jet OLEDB:System database=CataAccess2000MIS.mdw;" & _
    "User ID=myUser;Password=myPassword;"

    Set Cn = CreateObject("ADODB.Connection")
    Cn.Open myConnString 'Create DB connection
    Set Rs = CreateObject("ADODB.Recordset")

    With Rs
    Set .ActiveConnection = Cn
    .Source = mySql
    .Open , , 3, 3 '.Open , , adOpenStatic, adLockOptimistic
    myCnt = .RecordCount
    If myCnt > 0 Then
    .MoveLast: .MoveFirst
    tgtSh.Range(Cells(1, 1), Cells(myCnt, 1)).CopyFromRecordset Rs
    End If
    .Close
    End With</pre>



    Thank you very much in advance
    Regards,
    Martin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid NZ function (Excel/Access/VBA 2k)

    Apparently, the OLE DB driver doesn't know about the Access-specific Nz function; ODBC has or had the same problem. Try modifying the query/queries to use IIf and IsNull instead of Nz.

  3. #3
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid NZ function (Excel/Access/VBA 2k)

    Thank you very much Hans,
    I am not very good in understanding how OLE DB works and I thought that early binding could solve the problem but apparently it did not.

    maybe you could help with this one also:
    As my database is getting bigger, I also thought of using external data method of Excel, but it won't let me in the user-level secured database. Access obviously opens with system.mdw by default and I cannot use Get External Data.
    How do you get around this?

    Thank you so much
    Regards,
    Martin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid NZ function (Excel/Access/VBA 2k)

    If you're doing this from Excel, set up a new ODBC data source; there is an option to specify a workgroup file.
    Attached Images Attached Images
    • File Type: png x.png (12.1 KB, 1 views)

  5. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Prague, Czech Republic
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid NZ function (Excel/Access/VBA 2k)

    Thank you very much Hans,
    obviously I must have ODBC installed, ODBC Add-in is not enough (?).
    Martin
    Regards,
    Martin

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid NZ function (Excel/Access/VBA 2k)

    If you have Office 2000 Pro, you should have the ODBC drivers for Access etc.

    The screenshot is what I got when I selected Data | Get External Data | New Database Query..., double clicked <New Data Source>, entered a name, selected the Microsoft Access Driver (*.mdb) and clicked Connect.

    Alternatively, you can set up the ODBC driver in the ODBC Data Sources control panel. And it would even be possible to create the data source directly in the registry, or using VBA code.

Posting Permissions

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