http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en
microsoft.public.access.modulesdaovba@googlegroups.com
Today's topics:
* Code for List Boxes - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/6d34394630d78ba2?hl=en
* create function problem - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e070dedb6988e50f?hl=en
* drop table, import delimited text - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/83135dd9409f67e5?hl=en
* Why my code take time 10 Seconds - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/798c11ebb2454bf2?hl=en
* MSAccess Query as recordset - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/13065fc90d37f3bf?hl=en
* Undefine Function "FunctionName" in expression - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/9324a3418a405bf3?hl=en
* Compressing/Zipping Files from Access - 1 messages, 1 author
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f5683b004bd0d744?hl=en
==============================================================================
TOPIC: Code for List Boxes
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/6d34394630d78ba2?hl=en
==============================================================================
== 1 of 1 ==
Date: Thurs, Nov 8 2007 8:49 pm
From: Stu
I have a bound form that I am trying to filter records based on selection of
single or multiple rows from single or multiple list boxes. The code works
well with one box only. When I added code for the second list box, no
different results displayed. It obviously doesn't work. Can anyone assist
me where I went wrong and steer me in the right direction?
Private Function SelectListBox(xlstListBox As ListBox) As Long
' *** THIS FUNCTION RETURNS THE NUMBER OF ITEMS SELECTED IN A LISTBOX.
Dim xlngSelected As Long
Dim xvarSelected As Variant
On Error Resume Next
xlngSelected = 0
For Each xvarSelected In xlstListBox.ItemsSelected
xlngSelected = xlngSelected + 1
Next xvarSelected
SelectListBox = xlngSelected
Err.Clear
End Function
Private Function WhereString() As String
Dim strWhere As String
Dim strWhere1 As String
Dim varItem As Variant
On Error Resume Next
strWhere = ""
' ... build "Make" criterion expression
If SelectListBox(Me.lstMake) <> 0 Then
strWhere = strWhere & "Make IN ("
For Each varItem In Me.lstMake.ItemsSelected
strWhere = strWhere & "'" & _
Me.lstMake.ItemData(varItem) & "', "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - Len(", ")) & ") And "
End If
' Strip off the trailing " And " text string
If Len(strWhere) > 0 Then strWhere = Left(strWhere, Len(strWhere) - _
Len(" And "))
' ... build "Model" criterion expression
If SelectListBox(Me.lstModel) <> 0 Then
strWhere1 = strWhere1 & "Model IN ("
For Each varItem In Me.lstModel.ItemsSelected
strWhere1 = strWhere1 & "'" & _
Me.lstModel.ItemData(varItem) & "', "
Next varItem
strWhere1 = Left(strWhere1, Len(strWhere1) - Len(", ")) & ") And "
End If
' Strip off the trailing " And " text string
If Len(strWhere1) > 0 Then strWhere1 = Left(strWhere1, Len(strWhere1) - _
Len(" And "))
WhereString = strWhere
If Len(WhereString) > 0 And Len(strWhere1) > 0 Then
strWhere = strWhere & " AND " & strWhere1
Else
strWhere = strWhere & strWhere1
End If
Exit Function
End Function
Private Sub cmdSearch_Click()
Dim strSQL As String
Dim strRecordSource As String
On Error Resume Next
strRecordSource = "qryModelSearchTEST"
' move focus to clear button
Me.cmdClear.SetFocus
' build sql string for form's RecordSource
strSQL = WhereString
strSQL = "SELECT * FROM " & strRecordSource & _
IIf(strSQL = "", "", " WHERE ") & strSQL & ";"
Me.RecordSource = ""
Me.RecordSource = strSQL
Call SetVisibility(True)
End Sub
Any help is appreciated. Thanks
==============================================================================
TOPIC: create function problem
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e070dedb6988e50f?hl=en
==============================================================================
== 1 of 1 ==
Date: Thurs, Nov 8 2007 9:24 pm
From: "tina"
you're welcome :)
"Andy" <Andy@discussions.microsoft.com> wrote in message
news:F1C8C7C3-D9C8-44FD-BAED-4FBFD7ABA649@microsoft.com...
> It works perfectly.
> Thanks soooooooooo much, Tina.
>
> "tina" wrote:
>
> > okay, you want a custom function that returns a calculated value to each
> > record in the query's recordset. forget opening recordsets and using
> > querydefs in the function - you already have the data available within
the
> > query. just pass the data to the public function using arguments,
calculate
> > the value, and return it to the query, as
> >
> > Public Function dailysalary(ByVal sglStart As Single, _
> > ByVal sglEnd As Single, ByVal sglSalary As Single) As Single
> >
> > If sglStart > 8 And sglStart < 13 And _
> > sglEnd > 13 And sglEnd < 19 Then
> > dailysalary = (13 - sglStart + sglEnd - 14) * sglSalary / 8
> > ElseIf sglStart > 8 And sglEnd < 14 Then
> > dailysalary = (sglEnd - sglStart) * sglSalary / 8
> > ElseIf sglStart > 13 And sglEnd < 19 Then
> > dailysalary = (sglEnd - sglStart) * sglSalary / 8
> > ElseIf sglStart > 8 And sglStart < 13 And _
> > sglEnd > 13 And sglEnd > 19 And sglEnd <= 24 Then
> > dailysalary = (13 - sglStart + 18 - 14) * sglSalary / 8 +
> > (sglEnd - 18) * sglSalary / 8 * 1.5
> > ElseIf sglStart > 8 And sglStart < 13 And _
> > sglEnd > 13 And sglEnd > 19 And sglEnd > 24 Then
> > dailysalary = (13 - sglStart + 18 - 14) * sglSalary / 8 +
(24 -
> > 18) * sglSalary / 8 * 1.5 + (sglEnd - 24) * sglSalary / 8 * 2
> > ElseIf sglStart > 13 And sglEnd > 18 And sglEnd <= 24 Then
> > dailysalary = (18 - sglStart) * sglSalary / 8 + (sglEnd - 18) *
> > sglSalary / 8 * 1.5
> > ElseIf sglStart > 13 And sglEnd > 18 And sglEnd > 24 Then
> > dailysalary = (18 - sglStart) * sglSalary / 8 + (24 - 18) *
> > sglSalary / 8 * 1.5 +
> > (sglEnd - 24) * sglSalary / 8 * 2
> > Else
> > dailysalary = 0
> > End If
> >
> > End Function
> >
> > make each equation (dailysalary = "a math expression") run on one line,
> > regardless of linewrap here.
> >
> > add the calculated field to the query's Design grid, as
> >
> > [daily total]: dailysalary([START TIME], [END TIME], [SALARY])
> >
> > hth
> >
> >
> > "Andy" <Andy@discussions.microsoft.com> wrote in message
> > news:6AEFCAF8-4D49-41FC-8CDF-B9C977712795@microsoft.com...
> > > Sorry for the confusion! Tina,
> > > Actually I would like the calculated filed [daily total] show on the
> > query.
> > >
> > > For example :
> > > [daily total] : dailysalary()
> > >
> > > It does not work as per the error message#3027,
> > > I believe that I have something wrong, could you please correct me?
> > >
> > > Thanks again!
> > >
> > > "tina" wrote:
> > >
> > > > well, you could open a second recordset on the one table, and update
> > that;
> > > > it would probably involve doing a Find action to get to the
appropriate
> > > > record in the second recordset, within each loop of the first
recordset.
> > > >
> > > > but, generally speaking, you shouldn't store calculated data in a
table
> > at
> > > > all. it violates normalization rules, and always creates the
potential
> > for
> > > > inconsistent - and therefore inaccurate - data. i'd strongly
recommend
> > > > against it, in most cases.
> > > >
> > > > hth
> > > >
> > > >
> > > > "Andy" <Andy@discussions.microsoft.com> wrote in message
> > > > news:D74BE274-F764-4A0A-9872-F1A5E72C11D0@microsoft.com...
> > > > >
> > > > > Have added the new line within the loop as below :
> > > > > Do
> > > > > .....
> > > > > .....
> > > > > rst![daily total] = mysalary 'add new line here
> > > > >
> > > > > rst.MoveNext
> > > > > Loop Until rst.EOF
> > > > > .........
> > > > > .........
> > > > >
> > > > > But find the error message :
> > > > > "error#3027, the query can not be updated"
> > > > >
> > > > > Have mentioned that the query is based on 2 tables, so I can not
> > update
> > > > the
> > > > > table by this query, could you please advise and teach me what I
can
> > do?
> > > > or
> > > > > suggest any best solution?
> > > > >
> > > > > Thanks so much!
> > > > >
> > > > > "tina" wrote:
> > > > >
> > > > > > > I would like the value of dailysalary is going to equal the
value
> > of
> > > > > > > mysalary *from EACH record in the recordset* show in the query
> > "salary
> > > > > > total"
> > > > > >
> > > > > > *how are you going to use each value* as you retrieve it from
each
> > > > record?
> > > > > > if you're going to do something with it, then you must do it
within
> > the
> > > > > > loop, NOT after the loop is completed.
> > > > > >
> > > > > > hth
> > > > > >
> > > > > >
> > > > > > "Andy" <Andy@discussions.microsoft.com> wrote in message
> > > > > > news:4D24F624-928F-4EF5-B75F-3FCD908C6CC4@microsoft.com...
> > > > > > > Thanks, Tina.
> > > > > > >
> > > > > > > Please ingore the "[work details]" (I have removed) and
exactly
> > what
> > > > you
> > > > > > > said the value of dailysalary is always going to equal the
value
> > of
> > > > > > mysalary
> > > > > > > *from the last record in the recordset*.
> > > > > > >
> > > > > > > I would like the value of dailysalary is going to equal the
value
> > of
> > > > > > > mysalary *from EACH record in the recordset* show in the query
> > "salary
> > > > > > total"
> > > > > > > but I do not know what I can do?? Very appreciate for your
expert
> > > > > > advise!!!
> > > > > > >
> > > > > > > Thanks again!
> > > > > > >
> > > > > > > "tina" wrote:
> > > > > > >
> > > > > > > > well, "it does not work" doesn't give us much to go on.
lacking
> > any
> > > > > > details
> > > > > > > > of the problem you're seeing, my only comment is that you're
> > looping
> > > > > > through
> > > > > > > > one or more records in the recordset and setting the value
of
> > > > mysalary
> > > > > > on
> > > > > > > > each pass - but not putting that value anywhere. so the
value of
> > > > > > dailysalary
> > > > > > > > is always going to equal the value of mysalary *from the
last
> > record
> > > > in
> > > > > > the
> > > > > > > > recordset*.
> > > > > > > >
> > > > > > > > beyond that, i'm lost on where you're getting rst![work
> > > > details]![START
> > > > > > > > TIME]. presumably [START TIME] is a field in query "salary
> > total",
> > > > which
> > > > > > is
> > > > > > > > the query your recordset is based on. but what is [work
details]
> > > > > > referring
> > > > > > > > to?
> > > > > > > >
> > > > > > > > hth
> > > > > > > >
> > > > > > > >
> > > > > > > > "Andy" <Andy@discussions.microsoft.com> wrote in message
> > > > > > > > news:F5A0B87A-083B-46EF-B45C-96C71D2E8E1C@microsoft.com...
> > > > > > > > > Hi there,
> > > > > > > > >
> > > > > > > > > I have a query "salary total"(2 tables) , I would like to
> > create a
> > > > > > > > function
> > > > > > > > > to calculate the salary but it does not work, code as
follows.
> > > > > > Appreciate
> > > > > > > > for
> > > > > > > > > your expert help!
> > > > > > > > >
> > > > > > > > > Function dailysalary()
> > > > > > > > >
> > > > > > > > > Dim mysalary As Single
> > > > > > > > > Dim db As DAO.Database
> > > > > > > > > Dim rst As DAO.Recordset
> > > > > > > > > Dim qdf As DAO.QueryDef
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Set db = CurrentDb
> > > > > > > > > Set qdf = db.QueryDefs("salary total")
> > > > > > > > > Set rst = qdf.OpenRecordset
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > Do
> > > > > > > > >
> > > > > > > > > If rst![work details]![START TIME] > 8 And rst![work
> > > > details]![START
> > > > > > TIME]
> > > > > > > > <
> > > > > > > > > 13 And rst![work details]![END TIME] > 13 And rst![work
> > > > details]![END
> > > > > > > > TIME] <
> > > > > > > > > 19 Then
> > > > > > > > > mysalary = (13 - rst![work details]![START TIME] +
rst![work
> > > > > > details]![END
> > > > > > > > > TIME] - 14) * rst![work details]![SALARY] / 8
> > > > > > > > >
> > > > > > > > > ElseIf rst![work details]![START TIME] > 8 And rst![work
> > > > details]![END
> > > > > > > > TIME]
> > > > > > > > > < 14 Then
> > > > > > > > > mysalary = (rst![work details]![END TIME] - rst![work
> > > > details]![START
> > > > > > > > TIME])
> > > > > > > > > * rst![work details]![SALARY] / 8
> > > > > > > > >
> > > > > > > > > ElseIf rst![work details]![START TIME] > 13 And rst![work
> > > > > > details]![END
> > > > > > > > > TIME] < 19 Then
> > > > > > > > > mysalary = (rst![work details]![END TIME] - rst![work
> > > > details]![START
> > > > > > > > TIME])
> > > > > > > > > * rst![work details]![SALARY] / 8
> > > > > > > > >
> > > > > > > > > ElseIf rst![work details]![START TIME] > 8 And rst![work
> > > > > > details]![START
> > > > > > > > > TIME] < 13 And rst![work details]![END TIME] > 13 And
> > rst![work
> > > > > > > > details]![END
> > > > > > > > > TIME] > 19 And rst![work details]![END TIME] <= 24 Then
> > > > > > > > > mysalary = (13 - rst![work details]![START TIME] + 18 -
14) *
> > > > > > rst![work
> > > > > > > > > details]![SALARY] / 8 + (rst![work details]![END TIME] -
18) *
> > > > > > rst![work
> > > > > > > > > details]![SALARY] / 8 * 1.5
> > > > > > > > >
> > > > > > > > > ElseIf rst![work details]![START TIME] > 8 And rst![work
> > > > > > details]![START
> > > > > > > > > TIME] < 13 And rst![work details]![END TIME] > 13 And
> > rst![work
> > > > > > > > details]![END
> > > > > > > > > TIME] > 19 And rst![work details]![END TIME] > 24 Then
> > > > > > > > > mysalary = (13 - rst![work details]![START TIME] + 18 -
14) *
> > > > > > rst![work
> > > > > > > > > details]![SALARY] / 8 + (24 - 18) * rst![work
> > details]![SALARY] /
> > > > 8 *
> > > > > > 1.5
> > > > > > > > +
> > > > > > > > > (rst![work details]![END TIME] - 24) * rst![work
> > details]![SALARY]
> > > > / 8
> > > > > > * 2
> > > > > > > > >
> > > > > > > > > ElseIf rst![work details]![START TIME] > 13 And rst![work
> > > > > > details]![END
> > > > > > > > > TIME] > 18 And rst![work details]![END TIME] <= 24 Then
> > > > > > > > > mysalary = (18 - rst![work details]![START TIME]) *
rst![work
> > > > > > > > > details]![SALARY] / 8 + (rst![work details]![END TIME] -
18) *
> > > > > > rst![work
> > > > > > > > > details]![SALARY] / 8 * 1.5
> > > > > > > > >
> > > > > > > > > ElseIf rst![work details]![START TIME] > 13 And rst![work
> > > > > > details]![END
> > > > > > > > > TIME] > 18 And rst![work details]![END TIME] > 24 Then
> > > > > > > > > mysalary = (18 - rst![work details]![START TIME]) *
rst![work
> > > > > > > > > details]![SALARY] / 8 + (24 - 18) * rst![work
> > details]![SALARY] /
> > > > 8 *
> > > > > > 1.5
> > > > > > > > +
> > > > > > > > > (rst![work details]![END TIME] - 24) * rst![work
> > details]![SALARY]
> > > > / 8
> > > > > > * 2
> > > > > > > > >
> > > > > > > > > Else
> > > > > > > > > mysalary = 0
> > > > > > > > > End If
> > > > > > > > >
> > > > > > > > > rst.MoveNext
> > > > > > > > > Loop Until rst.EOF
> > > > > > > > >
> > > > > > > > > dailysalary = mysalary
> > > > > > > > >
> > > > > > > > > End Function
> > > > > > > > >
> > > > > > > >
> > > > > > > >
==============================================================================
TOPIC: drop table, import delimited text
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/83135dd9409f67e5?hl=en
==============================================================================
== 1 of 1 ==
Date: Thurs, Nov 8 2007 10:40 pm
From: John Nurick
Sorry - I got distracted and answered someone else's question<g>.
I've never worked with Oracle, but Access uses ODBC to work with an
Oracle database. You could set up a linked table in Access connected
to a view into the Oracle data, or write VBA code to use the
ADODB.Connection object. See
http://www.carlprothman.net/Default.aspx?tabid=81 for ODBC connection
string formats.
I don't know why Access has never had a 'macro' recorder. There may be
some deep technical reason going back to the days before the concept
of 'Microsoft Office' was invented, when Word, Excel, Access and
PowerPoint were developed independently.
Also, the correspondence between actions in the UI and VBA or SQL code
is much weaker when it comes to database operations. For example, if
the task is to update records according to some criterion, the UI
options are basically
(a) Open the table in datasheet view, filter it and use the
Edit|Replace command. This isn't a sane basis for updating records
under program control.
(b) Create the appropriate update query and execute it. If this
process was recorded, a duplicate query would be created every time
the 'macro' was run, which again doesn't make sense.
(c) Open a recordset on the table and write and execute the SQL
statement corresponding to the query in (b). But doing this means
executing VBA statements in the Immediate pane - so you're past the
stage of needing a recorder!
On Thu, 8 Nov 2007 14:11:01 -0800, mark
<mark@discussions.microsoft.com> wrote:
>okay, I'm using Access 2000, so I think you're telling me about a problem I
>might have had, with the path info.
>
>> Normally it's bad practice to drop a table and create a replacement as
>> part of ordinary operations. Instead, use a delete query to empty the
>> table and then import the new data to the same table.
>
>Okay, thanks for mentioning that. I guess it makes sense, but like I said,
>although I have a bunch of experience automating Excel, Access is new to me.
>
>I'm having some data import errors, but I'll get around them. Excel handles
>them fine. It's just type conversion stuff.
>
>I'll look into how to do a delte query, and then work on the code to import
>the new data, soon.
>
>All I've ever done with Access code was a line or two stuff here or there,
>and usually called that from an Access instance I initiated in Excel. A time
>or two, I've called someone else's code that they already wrote in Access.
>
>But that's it.
>
>Can you offer me an explanation as to why Access won't let you record VBA
>code, yet knows all about how to convert a 'Macro' to a 'Module' ? (If you
>just say, "Microsoft wrote it that way.", okay, but I'm wondering if there's
>a background logical reason?
>
>Obviously, recorded code is junk as far as end product applications, but if
>you're trying to do something you never did before, it can often give you a
>clue on how one might approach that task.
>
>Thanks for the help.
--
John Nurick - Access MVP
==============================================================================
TOPIC: Why my code take time 10 Seconds
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/798c11ebb2454bf2?hl=en
==============================================================================
== 1 of 1 ==
Date: Thurs, Nov 8 2007 10:42 pm
From: Marshall Barton
Nova wrote:
>I need to insert data to table intermod and write code in cal button click
>event. Unfortunately It takes time more than 20 seconds. I try many times
>and belives that "Insert into...." in M2FSql1 take almost of time.
>How to write code to reduce time.
>
>Dim M2FSql1 as String
>
>N=35
>For M = 0 To N - 1
> For X = M + 2 To N
> For Q = 1 To 3
>
> F1=M*N+5
> F2=M+1-N*2
> OrderN = 2 * Q + 1
> cal1 = (Q + 1) * F1 - Q * F2
> cal2 = (Q + 1) * F2 - Q * F1
>
>M2FSql1 = "INSERT INTO Intermod (OrderN,IntmodFreq, Freq1, Freq2) "
>M2FSql1 = M2FSql1 & "Values(" & OrderN & "," & cal1 & "," & F1 & "," & F2 &
>" );"
>DoCmd.RunSQL M2FSql1
> Next Q
> Next X
>Next M
I think this approach might be faster:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = OpenRecordset(Intermod, dbOpenDynaset)
N=35
For M = 0 To N - 1
For X = M + 2 To N
For Q = 1 To 3
rs.AddNew
F1=M*N+5
F2=M+1-N*2
rs!OrderN = 2 * Q + 1
rs!IntmodFreq= (Q + 1) * F1 - Q * F2
' cal2 = (Q + 1) * F2 - Q * F1
rs!Freq1 = F1
rs!Freq2 = F2
rs!Update
Next Q
Next X
Next M
rs.Close : Set rs = Nothing
Set db = Nothing
--
Marsh
MVP [MS Access]
==============================================================================
TOPIC: MSAccess Query as recordset
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/13065fc90d37f3bf?hl=en
==============================================================================
== 1 of 1 ==
Date: Thurs, Nov 8 2007 10:57 pm
From: John Nurick
If you just want to find out if there are any 'overlapping' records,
presumably all you need to do is to see if query1 returns any records.
You can do this with
If DCount("*", "query1") > 0 Then
...
The usual way to open a recordset on a query is along these lines:
Dim rsR As DAO.Recordset
Dim NumRecords As Long
Dim SomeNumber As Long
Set rsR = CurrentDB.OpenRecordset("MyQuery")
If rsR.EOF Then ' it's empty
NumRecords = 0
Else
'There is at least one record in the recordset
With rsR
'Move to the end to ensure we have an accurate record count
.MoveLast
NumRecords = .RecordCount
.MoveFirst
'Access field in current record
SomeNumber = .Fields("SomeNumber").Value
...
End With
End If
On Thu, 8 Nov 2007 14:29:00 -0800, Robert_L_Ross
<RobertLRoss@discussions.microsoft.com> wrote:
>I can't figure out how to do this!
>
>Here's my situation:
>I have a form (form1) that displays records (from table1) associated with a
>specific record on a different table (table2). In the header of form1 I have
>fields where the user can 'build' a new record to add to table1.
>
>I want to verify before adding the new record that it doesn't conflict with
>an existing record on table1 (basically, that the start/end dates don't
>overlap a record in the table1).
>
>I have a query (query1) in Access that I can open to see if the existing new
>record (from form1) has dates that overlap an existing record of the same
>type in table1.
>
>I want to have the button from form1 that you click to add the record first
>open query1 and see if it returned any results (found an overlapping record).
> If it does, I will show a warning and stop the process. If no overlapping
>records are found, I'll kick off an append query to add the records, then
>refresh my form to show the new record.
>
>I can't for the life of me get the query to open as a recordset. I must
>have gone through a dozen examples on this part of the group and none work.
>
>Any ideas?
--
John Nurick - Access MVP
==============================================================================
TOPIC: Undefine Function "FunctionName" in expression
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/9324a3418a405bf3?hl=en
==============================================================================
== 1 of 1 ==
Date: Thurs, Nov 8 2007 11:32 pm
From: nonlinear
Yes, this is a solution but a very slow solution and not so elegant..
I give the Microsoft URL (http://support.microsoft.com/kb/294698) in witch I
read the:
"Jet 4.0 Service Pack 8 now permits the execution of user-defined functions"
for your comments
Thanks any way...
"George Nicholson" wrote:
> for clarity, I probably should have said:
> "...you need to create an instance of Access via Automation, open the
> mdb within Access, run the query and grab the results."
>
> --
> HTH,
> George
>
>
> "George Nicholson" <GeorgeNJunk@Junkmsn.com> wrote in message
> news:%23TE3U3iIIHA.5904@TK2MSFTNGP04.phx.gbl...
> > In my experience:
> >
> > If you need query results that involve a user defined function while in an
> > external program (say, Excel), you need to open an instance of Access via
> > Automation, run the query and grab the results.
> >
> > --
> > HTH,
> > George
> >
> >
> > "nonlinear" <nonlinear@discussions.microsoft.com> wrote in message
> > news:7FFD46F0-CFC9-44AC-B10F-87332EC10D83@microsoft.com...
> >> Hello everybody,
> >> I would like to inform you that this question is not for novices...
> >> I have an Access query that uses a VBA public user-defined function
> >> (reside
> >> in an module) to calculate one query's field. When I directly run the
> >> query
> >> everything is ok. BUT..when the query is run from outside the ACCESS (for
> >> example through VB6 ADO code to result a recordset: rs.open "query") then
> >> the
> >> jet engine issues an error message: "Undefined Function 'FunctionName' in
> >> expression". It seeems that jet engine doesn't execute the function if
> >> the
> >> query is run outside the Access. I have searched the web and I found some
> >> information but we will analyze them after your comments.
> >
> >
>
>
>
==============================================================================
TOPIC: Compressing/Zipping Files from Access
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f5683b004bd0d744?hl=en
==============================================================================
== 1 of 1 ==
Date: Fri, Nov 9 2007 12:05 am
From: "Albert D. Kallal"
"Alan" <Alan@discussions.microsoft.com> wrote in message
news:704984AD-3C2F-4441-99A9-52A5143E97FC@microsoft.com...
> Im sorry ... I may be somewhat thick here, however I am looking for a way
> to
> compress csv files after I have imported them to access...
You mean exported data from MS access right?
Why on earth would you take some data file, and then put it inside MS access
as a compressed zip file?
What possible benefit or advantage would these files have?
You'll not be able to open and query or report or use this data inside of
ms-access with any of that tools that MS access has for reporting on data.
However if you must take some files and compress them before you import them
into some kind of binary objects inside MS access, then use my zippling
libary code for ms-access. you can find it here:
I have a zipping routine here:
http://www.members.shaw.ca/AlbertKallal/zip/index.htm
The above runs without you having to install winzip.
(and, you don't have to use shell coomands to
pkzip or installing pkzip).
The above download does require that you do a place a DLL in the same
directory as the MDB, or MDE file, but there's no softer installation of any
kind.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
==============================================================================
You received this message because you are subscribed to the Google Groups "microsoft.public.access.modulesdaovba"
group.
To post to this group, visit http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en
To unsubscribe from this group, send email to microsoft.public.access.modulesdaovba-unsubscribe@googlegroups.com
To change the way you get mail from this group, visit:
http://groups.google.com/group/microsoft.public.access.modulesdaovba/subscribe?hl=en
To report abuse, send email explaining the problem to abuse@googlegroups.com
==============================================================================
Google Groups: http://groups.google.com?hl=en
Tidak ada komentar:
Posting Komentar