Larry Linson wrote:
I had really thought you were "here to help," Lyle, but perhaps I was
mistaken.
UPDATE [SELECT s.*, ns.* FROM Suppliers AS s INNER JOIN
Northwind.Mdb.S uppliers AS ns ON s.SupplierID=ns .SupplierID]. AS
SubQuery SET s.CompanyName = ns.CompanyName;
The syntax here must be exact.
If you are using JET 4.0 then you are laughing because you can
substitute ( ) for [ ] for the subquery and omit the "." and alias as
in:
UPDATE (SELECT s.*, ns.* FROM Suppliers AS s INNER JOIN
Northwind.Mdb.S uppliers AS ns ON s.SupplierID=ns .SupplierID) SET
s.CompanyName=n s.CompanyName
This would mean that you can use [] to delimit your external db,
required if the path has spaces. [] are not nestable as delimiters
Application.Fol lowHyperlink "C:\Documen ts and Settings\Lyle
Fairfield\My Documents\Word\ Materialism.doc "
Split should be available in any version of Access >= 2000 (9).
If for some reason (I can't think of one) you need a UDF you could use
one of the many you could find with a Google search. Here's mine ...
not so extensively tested:
' change this if it does not meet your needs
Const SplitLimit As Long = 4096
Public Function SplitB(ByVal SplitString As String, _
Optional ByVal Delimiter As String = " ", _
Optional Element As Long) As Variant
Static aSplit() As Variant
Dim Position As Long
If Element = 0 Then ReDim aSplit(SplitLim it)
Position = InStr(SplitStri ng, Delimiter)
If Position = 0 Then
aSplit(Element) = SplitString
ReDim Preserve aSplit(Element)
SplitB = aSplit
Else
aSplit(Element) = _
Trim(Left(Split String, Position - 1))
SplitB = _
SplitB(Mid$(Spl itString, Position + 1), Delimiter, Element + 1)
End If
End Function
I suggest a two step approach, eg:
With DBEngine(0)(0)
.Execute "DELETE * FROM Suppliers WHERE SupplierID IN (SELECT
SupplierID FROM Northwind.mdb.S uppliers)"
.Execute "INSERT INTO Suppliers SELECT * FROM
Northwind.mdb.S uppliers"
End With
This may seem inefficient, but TTBOMK it's what databases do for an
update anyway (that is mark for deletion and append).
Public Function ShowTime( _
ByVal hours As Long, _
ByVal minutes As Long, _
ByVal AmorPm As Long) As String
Dim d As Date
d = TimeSerial(hour s + (AmorPm - 1) * 12, minutes, 0)
ShowTime = Format(d, "hhnn")
' or
' ShowTime = Format(d, "hh:nn ampm")
End Function
As a last resort I would use an independent connection eg
Dim c As ADODB.Connectio n
Set c = New ADODB.Connectio n
With c
..Open _
"PROVIDER=SQLOL EDB.1;" _
& "INTEGRATED SECURITY=SSPI;" _
& "INITIAL CATALOG=FFDBA_E SO_LOCAL;" _
& "DATA SOURCE=FFDBA\SQ LEXPRESS"
..Execute "spShrinkDataba se"
End With
Private Sub SomeWouldCallAH ack()
Dim z As Long
On Error Resume Next
With References
.AddFromFile "C:\Documen ts and Settings\Lyle Fairfield\My
Documents\Acces s\ESO\EsoAdmin. adp"
.AddFromFile "C:\Program Files\Common Files\Microsoft
Shared\VBA\VBA6 \VBE6EXT.OLB"
With VBE.VBProjects( "EsoAdmin").VBC omponents
For z = 1 To .Count
Debug.Print .Item(z).Name
Next z
End With
.Remove References("VBI DE")
.Remove References("Eso Admin")
End With
On Error GoTo 0
End Sub
Could you be talked into
On Error Resume Next
Kill "C:\Documen ts and Settings\t83329 9\Desktop\Extra cts\*.*"
On Error Goto 0 'or somewher else
From: Peter Tyler - view profile
Date: Fri, Feb 10 2006 7:53 am
Email: "Peter Tyler" <PLTy...@gmail. com>
Groups: comp.databases. ms-access
Not yet rated
Rating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
I am having a hard time trying to code around this. I have a checkbox
field in a MSAccess table called "100Fund" which is identical in
structure to other checkbox fields in the same table. I have a combo
box listing the various fields in the table for the user to select as a
table filter. If I use the following code in VB6 - where the user
selects the field "100Fund" - I get the above error:
Dim varchoice As String
varchoice = cmbFilter.Text
Adodc1.RecordSo urce = "select * from [A Table] where " & varchoice & "
= True order by LastName"
Adodc1.Refresh
All other checkbox fields work fine when selected. I appreciate that
it has to do with the field name starting with "100" but varchoice is
clearly a string variable. Using Cstr() doesn't help.
Any ideas? Thanks in advance!
Peter Tyler
Reply Rate this post: Text for clearing space
From: Lyle Fairfield - view profile
Date: Fri, Feb 10 2006 8:00 am
Email: "Lyle Fairfield" <lylefairfi...@ aim.com>
Groups: comp.databases. ms-access
Not yet rated
Rating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author
You might try:
where [" & varchoice & "]
Reply Rate this post: Text for clearing space
From: Peter Tyler - view profile
Date: Sat, Feb 11 2006 1:23 am
Email: "Peter Tyler" <PLTy...@gmail. com>
Groups: comp.databases. ms-access
Not yet rated
Rating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
It works well. Thanks Lyle!
But why? I would normally only use square brackets where the field (or
table) name has a space.
Peter
Reply Rate this post: Text for clearing space
From: Lyle Fairfield - view profile
Date: Sat, Feb 11 2006 6:02 am
Email: "Lyle Fairfield" <lylefairfi...@ aim.com>
Groups: comp.databases. ms-access
Not yet rated
Rating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse | Find messages by this author
from help:
The setting or return value is a String that specifies a name. The name
must start with a letter. The maximum number of characters depends on
the type of object Name applies to, as shown in Remarks. It can include
numbers and underscore characters ( _ ) but can't include punctuation
or spaces.
Reply Rate this post: Text for clearing space
From: Peter Tyler - view profile
Date: Mon, Feb 13 2006 2:16 pm
Email: "Peter Tyler" <PLTy...@gmail. com>
Groups: comp.databases. ms-access
Not yet rated
Rating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
OK thanks.
Peter
