Excel for Life: 25 new messages in 15 topics

microsoft.public.access.modulesdaovba
http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

==============================================================================
TOPIC: Combining two tables into a query to make a report that displa
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/aef8f6b1a7a79fc8?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 14 2007 7:33 am
From: matt

Thanks. I have my Main form set up. Like that but now i am trying to creat a
button on my form that will display a report based on that current record
pulling from both tables.

"Jeff Boyce" wrote:

> Matt
>
> Create a new query. Add both tables. Join them on their related field(s).
> Add fields from both that you want in the report. Save and close the query.
>
> Create a new report. For a source, point at the query you just created.
>
> --
> Regards
>
> Jeff Boyce
> www.InformationFutures.net
>
> Microsoft Office/Access MVP
> http://mvp.support.microsoft.com/
>
> Microsoft IT Academy Program Mentor
> http://microsoftitacademy.com/
>
> "matt" <matt@discussions.microsoft.com> wrote in message
> news:B0D0F42D-C325-42C7-BAEE-16D381C0C6CB@microsoft.com...
> > I have a database that has a main table and sub-table. I am trying to
> create
> > a report that will display the current record and the related info on the
> > sub-form. I was trying to use a SQL/Query. Any help thanks.
>
>

== 2 of 2 ==
Date: Wed, Nov 14 2007 8:19 am
From: Klatuu

Create the query from your tables and use it as the record source of the
report.

The use the Where argument of the OpenReport method to specify the record.
It would be the primary key field of the main table.
strWhere = "[ClientID] = " & Me.txtClientID
Docmd.OpenReport "MyReport", , ,strWhere

--
Dave Hargis, Microsoft Access MVP

"matt" wrote:

> Thanks. I have my Main form set up. Like that but now i am trying to creat a
> button on my form that will display a report based on that current record
> pulling from both tables.
>
> "Klatuu" wrote:
>
> > Access will do that for you.
> > The Record Source of the main form should be the main table.
> > The Record Source of the sub form should be the child table.
> >
> > Sub forms are contained in a subform control. Two properties of the subform
> > control are Link Master Fields and Link Child Fields. Put the name of the
> > field in the main table that links to the child time in the Link Master
> > Fields. Put the name of the field in the child table that links the child to
> > the master in the Link Child Fields.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "matt" wrote:
> >
> > > I have a database that has a main table and sub-table. I am trying to create
> > > a report that will display the current record and the related info on the
> > > sub-form. I was trying to use a SQL/Query. Any help thanks.

==============================================================================
TOPIC: update a field
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/042244d8ae35cb9e?hl=en
==============================================================================

== 1 of 3 ==
Date: Wed, Nov 14 2007 7:46 am
From: JT

Is it programmatically possible to update a field in a table? I have a
single table with 2 fields. 1 field is "group" and the second field is
"branch". There is a single record in this field. These fields contain the
version number. As we release new versions I need to update the database for
each cost center. I would like a way to do this in a macro instead of going
in each database to change the version number.

Any help getting started would be appreciated. Thanks for the help....
--
JT

== 2 of 3 ==
Date: Wed, Nov 14 2007 8:01 am
From: "Alex Dybenko"

Hi,
make an update or append query and run it in macro or VBA

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"JT" <JT@discussions.microsoft.com> wrote in message
news:2C92B9C3-5D21-4687-8555-5A1AABC91E2B@microsoft.com...
> Is it programmatically possible to update a field in a table? I have a
> single table with 2 fields. 1 field is "group" and the second field is
> "branch". There is a single record in this field. These fields contain
> the
> version number. As we release new versions I need to update the database
> for
> each cost center. I would like a way to do this in a macro instead of
> going
> in each database to change the version number.
>
> Any help getting started would be appreciated. Thanks for the help....
> --
> JT

== 3 of 3 ==
Date: Wed, Nov 14 2007 10:44 am
From: "J_Goddard via AccessMonster.com"

Hi -

The description of your table seems strange. You said there are two fields
in the table, "Group" and "Branch". Each *record* in the table therefore
contains these two fields, so saying "There is a single record in this field"
makes no sense.

What data is the table supposed to contain? If you want to track what
version number each Branch/Group has, then you a third field in the table -
VersionNumber.

You can update the table with SQL, for example:

mySQL="UPDATE MyTable set VersionNumber = " & NewVersionNumber &
" WHERE Branch = '" & BranchName & "' AND Group = '" & GroupName & "'"

db.execute SQL

Please clarify if I don't have my assumptions right.

HTH

John

JT wrote:
>Is it programmatically possible to update a field in a table? I have a
>single table with 2 fields. 1 field is "group" and the second field is
>"branch". There is a single record in this field. These fields contain the
>version number. As we release new versions I need to update the database for
>each cost center. I would like a way to do this in a macro instead of going
>in each database to change the version number.
>
>Any help getting started would be appreciated. Thanks for the help....

--
Message posted via http://www.accessmonster.com

==============================================================================
TOPIC: OpenRecordset() can't take parameter inside query?
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/23fcb0b1da9699b3?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 7:59 am
From: "Dirk Goldgar"

In news:7A9B0B6B-D1D8-45F8-82DD-1073201E7F67@microsoft.com,
Meitatsu <Meitatsu@discussions.microsoft.com> wrote:
> I have the same problem that instead of SQL, I am trying to use a
> query with a criterion refering to a combo box in a form. like the
> following statement. Ofer's solution does work in case of SQL, does
> it mean that no way to use a query in such case?
> Any ideas would be much appreciated!
>
> Private Sub Command3_Click()
> On Error GoTo Err_Command3_Click
> Dim DBS As DAO.Database
> Dim rstA1, rstA2 As DAO.Recordset

**NOTE** the above line defines rstA2 as a DAO.Recordset, but defines
rstA1 only as a Variant. You should write this:

Dim rstA1 As DAO.Recordset, rstA2 As DAO.Recordset

> Dim stDocName, strQueryName, strQueryName1, strQRY As String

Similarly, the above line declares only strQRY as a String.

> Dim strB1 As String
> Dim stLinkCriteria As String
> Dim blnA1 As Boolean
> Dim bytB1 As Byte
> Dim lngA1 As Long
>
> strQueryName = "20211CreateAsmblySchedul"
> strQueryName1 = "20213AsmblySchedul"
> Set DBS = CurrentDb()
>
> 'Error message: too few parameters, expected 1
> Set rstA1 = DBS.OpenRecordset(strQueryName1)

Using a stored query, you have to explicitly resolve the parameters
before you open a recordset from it. You can do this by manipulating
the QueryDef object, like this:

Dim DBS As DAO.Database

Dim rstA1 As DAO.Recordset
Dim rstA2 As DAO.Recordset

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Dim stDocName As String
Dim strQueryName As String
Dim strQueryName1 As String
Dim strQRY As String
Dim strB1 As String
Dim stLinkCriteria As String
Dim blnA1 As Boolean
Dim bytB1 As Byte
Dim lngA1 As Long

strQueryName = "20211CreateAsmblySchedul"
strQueryName1 = "20213AsmblySchedul"
Set DBS = CurrentDb

Set qdf = DBS.QueryDefs(strQueryName1)
With qdf

' Resolve parameters.
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next prm

' Open recordset.
Set rstA1 = .OpenRecordset

End With

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

==============================================================================
TOPIC: Check SQL Server Table Permissions
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e82f6b4820a46875?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 8:21 am
From: "Alex Dybenko"

Hi,
You can just try to run SP or select table and catch error

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"BillyRogers" <BillyRogers@discussions.microsoft.com> wrote in message
news:7BB4DF4B-0753-437E-A194-1EF9CFD2D490@microsoft.com...
>I have an Access program that users outside of our office are using. The
> program calls stored procedures (using ADO) in SQL Server from Access.
> Last
> month the program didn't work and I finally tracked it down to the
> permissions having been dropped for the group from one of the tables.
> Our
> IT dept never explained to me how this happened, they just reset the
> permissions. I'm getting the same problem this month. The stored
> procedure uses a lot of tables.
>
> Is there some way to find out which table it is that the permissions are
> missing from? Or is there a way to check the persmissions before calling
> the stored procedure?
>
> I'm just trying to make this easier to troubleshoot if it happens again.
>
> Thanks,
> --
> Billy Rogers
>
> Dallas,TX
>
> Currently Using SQL Server 2000, Office 2000 and Office 2003

==============================================================================
TOPIC: CVar Function
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ae6dd3e67da3d929?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 9:45 am
From: CM

I have an append query that contains an expression as a field that will not
update without the CVar function being used. The expression is as follows:
MUserName: CVar([Forms]![frmfield]![UserName]). This expression will return
the username without the CVar function, but the append query will not update
without the function. Can anyone explain why?

Thanks.

--
CM

==============================================================================
TOPIC: Wrong number of arguments
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/ec084965993b2d1f?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 14 2007 9:46 am
From: "samotek via AccessMonster.com"

i get the error "wrong number of arguments or invalid proprty assignment"
Can you help me with this problem ?
Private Function KillObject(strDbName As String, acObjectType As Long,
strObjectName As String, StrPassword As String)
StrPassword = "secret"
Dim db As DAO.Database
Dim adb As Object
Set adb = CreateObject("Access.Application")
adb.OpenCurrentDatabase strDbName, , StrPassword
adb.DoCmd.DeleteObject acObjectType, strObjectName
adb.CloseCurrentDatabase
Set adb = Nothing
End Function

Private Sub Command2_Click()
Call KillObject(GPath, 0, "products", "secret")

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1

== 2 of 2 ==
Date: Wed, Nov 14 2007 10:25 am
From: "Douglas J. Steele"

According to the Help file, the OpenCurrentDatabase method only takes 2
arguments: the full path to the database, and (optionally) a boolean value
which specifies whether the database should be opened in exclusive mode.

To see how to open a password-protected database, see

http://support.microsoft.com/kb/235422

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"samotek via AccessMonster.com" <u15330@uwe> wrote in message
news:7b360769b1546@uwe...
>i get the error "wrong number of arguments or invalid proprty assignment"
> Can you help me with this problem ?
> Private Function KillObject(strDbName As String, acObjectType As Long,
> strObjectName As String, StrPassword As String)
> StrPassword = "secret"
> Dim db As DAO.Database
> Dim adb As Object
> Set adb = CreateObject("Access.Application")
> adb.OpenCurrentDatabase strDbName, , StrPassword
> adb.DoCmd.DeleteObject acObjectType, strObjectName
> adb.CloseCurrentDatabase
> Set adb = Nothing
> End Function
>
>
> Private Sub Command2_Click()
> Call KillObject(GPath, 0, "products", "secret")
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1
>

==============================================================================
TOPIC: Waiting for input from a form
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f3c5d8c996864ce6?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 14 2007 9:45 am
From: "Douglas J. Steele"

I read that as meaning he tried to set the form's Modal property to True,
which I believe is different that opening it using acDialog.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
wrote in message news:uhEZXCsJIHA.5928@TK2MSFTNGP05.phx.gbl...
> Since he said that modal mode didn't work <g>
> Can happen when timers are involved
>
> Pieter
>
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:uqQOhtrJIHA.4272@TK2MSFTNGP05.phx.gbl...
>> Why not open the form using acDialog?
>>
>> DoCmd.OpenForm "MyForm", WindowMode:=acDialog
>>
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no e-mails, please!)
>>
>>
>> "Pieter Wijnen"
>> <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
>> wrote in message news:e7YC3XqJIHA.4880@TK2MSFTNGP03.phx.gbl...
>>> Something Like
>>>
>>> DoCmd.OpenForm "MyForm"
>>>
>>> While FisLoaded("MyForm")
>>> DoEvents
>>> Wend
>>>
>>> Use your own favourite FisLoaded (thers's one at
>>> http://www.mvps.org/access)
>>>
>>> HtH
>>>
>>> Pieter
>>>
>>>
>>> "Rod" <Rod@discussions.microsoft.com> wrote in message
>>> news:107E9CE0-20E5-4575-8321-57B924622E3E@microsoft.com...
>>>> Half-way through my code, the user needs to supply information from a
>>>> combo-box.
>>>>
>>>> How do I pause my code while the user makes their selection? I cannot
>>>> use
>>>> InputBox or MsgBox, since I need the combobox object. How do I open an
>>>> ordinary form and then wait for the user to press "OK" (or whatever
>>>> button I
>>>> create) before continuing to execute the code? Setting the Modal
>>>> property to
>>>> Yes doesn't help.
>>>>
>>>> Thanks
>>>
>>>
>>
>>
>
>

== 2 of 2 ==
Date: Wed, Nov 14 2007 10:37 am
From: "Pieter Wijnen"

It shouldn't be, I think. At least not as far as I recall.
I do offcourse use the acDialog Param to make sure myself, but always also
add the (superfluos) waiting loop to make sure it works under any
circumstances.

Pieter

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:O$KSgYuJIHA.484@TK2MSFTNGP06.phx.gbl...
>I read that as meaning he tried to set the form's Modal property to True,
>which I believe is different that opening it using acDialog.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Pieter Wijnen"
> <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
> wrote in message news:uhEZXCsJIHA.5928@TK2MSFTNGP05.phx.gbl...
>> Since he said that modal mode didn't work <g>
>> Can happen when timers are involved
>>
>> Pieter
>>
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
>> news:uqQOhtrJIHA.4272@TK2MSFTNGP05.phx.gbl...
>>> Why not open the form using acDialog?
>>>
>>> DoCmd.OpenForm "MyForm", WindowMode:=acDialog
>>>
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no e-mails, please!)
>>>
>>>
>>> "Pieter Wijnen"
>>> <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
>>> wrote in message news:e7YC3XqJIHA.4880@TK2MSFTNGP03.phx.gbl...
>>>> Something Like
>>>>
>>>> DoCmd.OpenForm "MyForm"
>>>>
>>>> While FisLoaded("MyForm")
>>>> DoEvents
>>>> Wend
>>>>
>>>> Use your own favourite FisLoaded (thers's one at
>>>> http://www.mvps.org/access)
>>>>
>>>> HtH
>>>>
>>>> Pieter
>>>>
>>>>
>>>> "Rod" <Rod@discussions.microsoft.com> wrote in message
>>>> news:107E9CE0-20E5-4575-8321-57B924622E3E@microsoft.com...
>>>>> Half-way through my code, the user needs to supply information from a
>>>>> combo-box.
>>>>>
>>>>> How do I pause my code while the user makes their selection? I cannot
>>>>> use
>>>>> InputBox or MsgBox, since I need the combobox object. How do I open an
>>>>> ordinary form and then wait for the user to press "OK" (or whatever
>>>>> button I
>>>>> create) before continuing to execute the code? Setting the Modal
>>>>> property to
>>>>> Yes doesn't help.
>>>>>
>>>>> Thanks
>>>>
>>>>
>>>
>>>
>>
>>
>
>

==============================================================================
TOPIC: Please help me reguarding this matter. Thank You.
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a5ab286e9847cfff?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 9:48 am
From: "Douglas J. Steele"

You didn't really give enough information.

Do you need one date for, say, 10 rows, and a different date for another 12
rows? How will you know which is which?

You can always set the DefaultValue for the date field. You could also use
an Update query to set the date for all rows for which there isn't already a
date.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

<lasithakamalpriya@gmail.com> wrote in message
news:1195049652.042750.221850@e34g2000pro.googlegroups.com...
> Please any one help me with Ms Access 2002
> I want create a data table In which should be One Date & Many Items
> I don't want to enter the date Recordset by Recordset it is wasting
> the time
> Please tell me How I can do It. Just like using sub form. If you have
> any solution please send to my email address which is
> lasithakamalpriya@google.com

Sorry, that's not how newsgroups work. You post your question to the
newsgroup, you come back to the newsgroup to get your answer.

==============================================================================
TOPIC: VBA future in MS ACCESS (or VSTA)
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/027c7233fa631b2e?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 14 2007 10:18 am
From: "Tony Toews [MVP]"

Eric Decker <merquelra@hotmell.com> wrote:

>Has anyone heard if VSTA will ever be supported for MS ACCESS? I've heard
>rumors that VBA will not be supported for 64 bit and the VSTA will. Will MS
>Access someday support VSTA then?

What is VSTA? Visual Studio Tools for Applications? Do you have a
URL for that?

I have no idea. MS Office will be supporting VBA for the near future.
Given the hooting and hollering of the VB6 to VB.Net conversion I
don't think the Office folks are looking to repeat that experience.

64 bit what? A 64 bit operating system?

You can still run well behaved DOS apps inside Windows XP. So that's,
what 8 bit running inside. I can run A2.0 under Windows XP. There's
16 bit running.

While I haven't personally run it I'm sure that Access 97/2003, etc
would work well under a 64 bit OS. You'd have to run some of those
versions as administrator of course.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at

http://www.granite.ab.ca/accsmstr.htm

Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

== 2 of 2 ==
Date: Wed, Nov 14 2007 12:08 pm
From: "David W. Fenton"

"Tony Toews [MVP]" <ttoews@telusplanet.net> wrote in
news:61emj31ff43a12megrb9rl8pib7o3pa9ee@4ax.com:

> While I haven't personally run it I'm sure that Access 97/2003,
> etc would work well under a 64 bit OS. You'd have to run some of
> those versions as administrator of course.

Or tweak the registry permissions for non-admin users.

--
David W. Fenton

http://www.dfenton.com/

usenet at dfenton dot com

http://www.dfenton.com/DFA/

==============================================================================
TOPIC: DAO Recordset Type
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4e6c677318773579?hl=en
==============================================================================

== 1 of 4 ==
Date: Wed, Nov 14 2007 10:46 am
From: Pasquale

Hello,

In Access 2000 using DAO, I have two mdb files: one containing the tables
only and another one with VBA modules and query and so on.
I've noticed that when I link to tables from one mdb to another, Access
2000 will treat successive Recordsets instances in VBA as Dynasets
"automatically", that is, if I use Recordset.Type the value 2 is
returned, which stands for Dynaset.
I've noticed this because I had data and code all in one mdb file and
used Recordset.Recordcount, which returned the correct number of records
in a table, while, after separating data and code (thus linking the
tables to the code mdb file), the same code would return 1.
In the meantime I have found out that with Dynaset I must use
Recordset.MoveLast before using Recordset.Recordcout - Dynaset doesn't
fetch the entire Recordset by default.
I was wondering however, since this behavior is "automatic" is there no
way to tell Access 2000 to treat a table as "Table type" if it isn't
stored in the same mdb file?

TIA
p

== 2 of 4 ==
Date: Wed, Nov 14 2007 11:17 am
From: "Douglas J. Steele"

Once you've split the database into a front-end and a back-end, your
recordsets are no longer Table type. Table type, as you've found, only works
when everything's in a single MDB file (which, of course, isn't
recommended).

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Pasquale" <imbemba@yahoo.com> wrote in message
news:473b4280$0$4796$4fafbaef@reader4.news.tin.it...
> Hello,
>
> In Access 2000 using DAO, I have two mdb files: one containing the tables
> only and another one with VBA modules and query and so on.
> I've noticed that when I link to tables from one mdb to another, Access
> 2000 will treat successive Recordsets instances in VBA as Dynasets
> "automatically", that is, if I use Recordset.Type the value 2 is
> returned, which stands for Dynaset.
> I've noticed this because I had data and code all in one mdb file and
> used Recordset.Recordcount, which returned the correct number of records
> in a table, while, after separating data and code (thus linking the
> tables to the code mdb file), the same code would return 1.
> In the meantime I have found out that with Dynaset I must use
> Recordset.MoveLast before using Recordset.Recordcout - Dynaset doesn't
> fetch the entire Recordset by default.
> I was wondering however, since this behavior is "automatic" is there no
> way to tell Access 2000 to treat a table as "Table type" if it isn't
> stored in the same mdb file?
>
>
> TIA
> p

== 3 of 4 ==
Date: Wed, Nov 14 2007 11:18 am
From: "Dirk Goldgar"

In news:473b4280$0$4796$4fafbaef@reader4.news.tin.it,
Pasquale <imbemba@yahoo.com> wrote:
> I was wondering however, since this behavior is "automatic" is there
> no way to tell Access 2000 to treat a table as "Table type" if it
> isn't stored in the same mdb file?

Not without going around the barn a bit. You can't open a table-type
recordset on a linked table. If you really need to, you can open a
separate Database object on the database that contains the linked table,
then open a table-type recordset from that database object. That way,
you wouldn't be using a linked table. People do this sometimes when
they want to use the Seek method on a foreign table. However, if all
you want to know is the number of records in the table, I think it would
be preferable to just use a linked table and use MoveLast before
checking RecordCount, or else open a recordset on a SELECT COUNT(*)
query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

== 4 of 4 ==
Date: Wed, Nov 14 2007 11:38 am
From: Marshall Barton

Pasquale wrote:

>Hello,
>
>In Access 2000 using DAO, I have two mdb files: one containing the tables
>only and another one with VBA modules and query and so on.
>I've noticed that when I link to tables from one mdb to another, Access
>2000 will treat successive Recordsets instances in VBA as Dynasets
>"automatically", that is, if I use Recordset.Type the value 2 is
>returned, which stands for Dynaset.
>I've noticed this because I had data and code all in one mdb file and
>used Recordset.Recordcount, which returned the correct number of records
>in a table, while, after separating data and code (thus linking the
>tables to the code mdb file), the same code would return 1.
>In the meantime I have found out that with Dynaset I must use
>Recordset.MoveLast before using Recordset.Recordcout - Dynaset doesn't
>fetch the entire Recordset by default.
>I was wondering however, since this behavior is "automatic" is there no
>way to tell Access 2000 to treat a table as "Table type" if it isn't
>stored in the same mdb file?

Linked tables can not be accessed as a table type recordset.

You could open the backend mdb using OpenDatabase and then
use that database object to open a table type recordset, BUT
doing that just to avoid a MoveNext is not worth it. About
the only justification I am aware of is when you have an
overwhelming reason to use the Seek method.

--
Marsh
MVP [MS Access]

==============================================================================
TOPIC: Linked tables to SQL Server
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/fe3f43862f1fc69b?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 11:26 am
From: Weste

Wow I never would have guessed that. I turned off the office plug-in for
Norton and that fixed the problem. Thank you very much!

"Rick Brandt" wrote:

> Weste wrote:
> > I have Access 2003 and Access 2007 on my pc. I am using Access 2003
> > and am trying to connect to a database in SQL Express. I have
> > created a System DSN and successfully tested the connection.
> > However, when I select File | Get External Data | Link Tables | Files
> > of Type = ODBC Databases, the ODBC Datasource Administrator does not
> > appear. The link tables dialog just disappears. Does anyone know
> > of any issues with running 2003 and 2007 on the same pc?
> >
> > Also when I switch between versions, the installer always runs to
> > configure the version of Access I am opening. This is a pain. Is
> > there a way to stop this?
> >
> > Thanks for your help.
>
> Believe it or not it is likely your anti-virus software. I don't recall the
> specifics (someone else might chime in), but if you search these groups you
> should find the setting that needs to be changed.
>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com
>
>
>

==============================================================================
TOPIC: Table records are being deleted for unknown reason
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/85263686916f4e45?hl=en
==============================================================================

== 1 of 2 ==
Date: Wed, Nov 14 2007 11:50 am
From: "Bewilly"

Data does not mysteriously disappear. Someone (unknowingly or knowingly) is
deleting data either manually or there is some code that is triggering this.
You need to watch whoever is maintaining the data step by step.

Bruce wrote:
>I have a MSAccess 2003 database which is losing records from one of its
>tables (i.e. they are there 1 day but not the next).
>
>The user who maintains the data says they are not deleting the records and
>they are not getting any error messages. When they run reports they notice
>items are missing from time to time. Also, there is only 1 user who acessess
>this database.
>
>The database is hosted on a remote server on an intranet and has an average
>to slow response time (depending on traffic), even so the database is small
>under 2mb.
>
>Anyone come across anything like this before?
>What are some options to diagnose and solve the problem?
>
>Bruce

== 2 of 2 ==
Date: Wed, Nov 14 2007 12:16 pm
From: "Pieter Wijnen"

*could* be a sign of corruption (caused by bad Network card et al)
Make a Backup copy of the Db & do a Compact & Repair (should be done
regulary)

Pieter

"Bewilly" <u39062@uwe> wrote in message news:7b371c355709e@uwe...
> Data does not mysteriously disappear. Someone (unknowingly or knowingly)
> is
> deleting data either manually or there is some code that is triggering
> this.
> You need to watch whoever is maintaining the data step by step.
>
> Bruce wrote:
>>I have a MSAccess 2003 database which is losing records from one of its
>>tables (i.e. they are there 1 day but not the next).
>>
>>The user who maintains the data says they are not deleting the records and
>>they are not getting any error messages. When they run reports they notice
>>items are missing from time to time. Also, there is only 1 user who
>>acessess
>>this database.
>>
>>The database is hosted on a remote server on an intranet and has an
>>average
>>to slow response time (depending on traffic), even so the database is
>>small
>>under 2mb.
>>
>>Anyone come across anything like this before?
>>What are some options to diagnose and solve the problem?
>>
>>Bruce
>

==============================================================================
TOPIC: ISAM error when trying to create tablelink
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/3f2f7276868cdcb5?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 11:56 am
From: RobGMiller

I am having a similar problem linking tables to SQLServer or a different
access database. In my case, I get the ISAM error when I try to change the
tabledef.connect from one to the other.

The connection strings I use work if I am not changing from one type to
another.

ACCESS:
;Database=O:\FullPath\databasename.mdb

SQL
Driver={SQL Native Client}; Server=BizTalkServer\OfficeServers;
Database=EngManager;UID=limiteduser;PWD=password;Persist Security Info=True

So I can change to a different access database or I can change to a
different SQL server or database in the same server.

I've looked at Doug's code on

http://www.accessmvp.com/DJSteele/DSNLessLinks.html and I've tried other
methods.

Id doesn't matter if the table to be changed is deleted first or not. If the
tabledef is deleted the ISAM error occurs as the new tabledef is appended. If
the tabledef is not deleted the ISAM occures on Tabledef.RefreshLink

The simplified code comes down to the following when resetting the link
without deleting the existing tabledef:

tDef.Attributes = DB_ATTACHSAVEPWD
tDef.Connect = NewConnectionString
tDef.RefreshLink

And the following when deleting the tabledef:

DB.TableDefs.Delete (TableName)
DB.TableDefs.Refresh
tDef.Name = TableName
tDef.Attributes = DB_ATTACHSAVEPWD
tDef.SourceTableName = Owner & "." & TableName"
DB.TableDefs.Append tDef (error: Could not find installable isam)
DB.TableDefs(TableName).RefreshLink

This code and connect strings works well when not changing to a differnet
type of database.

Note: I know this works because I've done it before in a different
application but the same technique does not work in this database.

Thanks in advance for your help.

--
RobGMiller

"Douglas J. Steele" wrote:

> Sorry, I can't think of other possible solutions.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Mark" <Mark@discussions.microsoft.com> wrote in message
> news:8F43BD70-AD0C-46CF-A3C3-E285EE3D4342@microsoft.com...
> > My apologies for the late response. I had a high priority project due...
> >
> > I was successfully able to link and tried to copy the description in the
> > table design, but got the same issue.
> >
> > -Mark
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> Try creating a linked table through File | Get External Data | Link
> >> Tables.
> >> Assuming that works, examine the Connect property of the table you
> >> created.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> news:91B63395-65B9-427E-A454-2540FB264D92@microsoft.com...
> >> > Hi Doug,
> >> >
> >> > Tried that change and unfortunately still not working. Here's the full
> >> > sub,
> >> > with the password and user removed to protect the innocent. I tried
> >> > "ODBC;"
> >> > and "ODBC=;" and first one gave me another error, but the second one
> >> > worked.
> >> > I'm able to see the rs recordset, but appending the tdf tabledef causes
> >> > that
> >> > same ISAM issue.
> >> >
> >> > And thank you so much for your help.
> >> > -Mark
> >> >
> >> >
> >> > Sub ADOConnect()
> >> > Dim rs As New ADODB.Recordset
> >> > Dim db As Database
> >> > Dim conn As ADODB.Connection
> >> > Dim i As Integer, j As Integer
> >> > Dim str As String
> >> > Dim tdf As TableDef
> >> >
> >> > Set conn = New ADODB.Connection
> >> > str = "ODBC=;DSN=Strategy;USER=;PWD="
> >> >
> >> > conn.ConnectionString = str
> >> > conn.Open
> >> >
> >> > Set db = CurrentDb
> >> > Set tdf = db.CreateTableDef("TEST")
> >> > tdf.Connect = str
> >> > tdf.SourceTableName = "PINSQUERY"
> >> >
> >> > 'rs opens okay with the connection.
> >> > rs.Open "select * from usrqrycs.pinsquery", conn, adOpenStatic,
> >> > adLockReadOnly
> >> > Debug.Print rs(0).Name
> >> > Debug.Print rs(0).Value
> >> >
> >> > 'errors out here.
> >> > db.TableDefs.Append tdf
> >> >
> >> > End Sub
> >> >
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> I must have made a typo. That was supposed to be a semi-colon between
> >> >> ODBC
> >> >> and DSN=.
> >> >>
> >> >> Sorry about that.
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no e-mails, please!)
> >> >>
> >> >>
> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> news:E6171895-EA3A-4F5F-9595-A804DA929B9E@microsoft.com...
> >> >> > Hi Doug,
> >> >> >
> >> >> > So, I'm pretty much a newbie with this and I'm not sure I'm
> >> >> > following
> >> >> > your
> >> >> > suggestion correctly. I updated str so
> >> >> > str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> >> >> >
> >> >> > where the character between the ODBC and DSN is a pipe (it copies as
> >> >> > the
> >> >> > letter 'l'). I didn't change anything else in the code, but am still
> >> >> > getting
> >> >> > the same error. Was there something else I needed to change?
> >> >> >
> >> >> > Oh, and yes, i'm using ADO.
> >> >> >
> >> >> > Thanks for taking my call!
> >> >> > -Mark
> >> >> >
> >> >> >
> >> >> > "Douglas J. Steele" wrote:
> >> >> >
> >> >> >> I'm assuming that your recordset is using ADO.
> >> >> >>
> >> >> >> What you've got appears to be an Ole DB connection string
> >> >> >> (actually,
> >> >> >> the
> >> >> >> MSDASQL Ole DB provider is considered obsolete by Microsoft: for
> >> >> >> replacements, see what Carl Prothman has at
> >> >> >> http://www.carlprothman.net/Technology/ConnectionStrings/OLEDBProviders/tabid/87/Default.aspx).
> >> >> >>
> >> >> >> When creating linked tables and/or pass-through queries, your only
> >> >> >> choice
> >> >> >> is
> >> >> >> ODBC. Since you apparently have a DSN created, try:
> >> >> >>
> >> >> >> str = "ODBClDSN=MYDSN;USER=USERNM;PWD=MYPASSWD"
> >> >> >>
> >> >> >>
> >> >> >> --
> >> >> >> Doug Steele, Microsoft Access MVP
> >> >> >> http://I.Am/DougSteele
> >> >> >> (no e-mails, please!)
> >> >> >>
> >> >> >>
> >> >> >> "Mark" <Mark@discussions.microsoft.com> wrote in message
> >> >> >> news:9F1E9B97-3C67-4A87-8E34-D03D6BD1624D@microsoft.com...
> >> >> >> > Hi,
> >> >> >> >
> >> >> >> > I'm using Access 2003 and trying to create a linked table from an
> >> >> >> > AS400
> >> >> >> > database.
> >> >> >> >
> >> >> >> > Here's what I have that's not working.
> >> >> >> > ===========
> >> >> >> > Dim db As Database
> >> >> >> > Dim tdf As TableDef
> >> >> >> > dim str as string
> >> >> >> >
> >> >> >> > str =
> >> >> >> > "Provider=MSDASQL;DSN=MYDSN;USER=USERNM;DATABASE=DB_QRY;PWD=MYPASSWD"
> >> >> >> >
> >> >> >> > Set tdf = db.CreateTableDef("TEST")
> >> >> >> > tdf.Connect = str
> >> >> >> > tdf.SourceTableName = "TBL1"
> >> >> >> > db.TableDefs.Append tdf
> >> >> >> > ===========
> >> >> >> >
> >> >> >> > When I get to the last line, I get the error "could not find
> >> >> >> > installable
> >> >> >> > ISAM"
> >> >> >> >
> >> >> >> > Now, two things
> >> >> >> > 1) if i use the same connectiong string (str) and open up a
> >> >> >> > recordset
> >> >> >> > object
> >> >> >> > to TBL1, I can successfully get to that table, so that tells me
> >> >> >> > the
> >> >> >> > connection string is okay.
> >> >> >> > 2) If I put in the wrong password in the connection string,
> >> >> >> > tdf.connect
> >> >> >> > doesn't give any error.
> >> >> >> >
> >> >> >> > Okay, any ideas how I can fix this?
> >> >> >> >
> >> >> >> > Thanks!
> >> >> >> > -Mark
> >> >> >> >
> >> >> >> >
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

==============================================================================
TOPIC: Insert large base64 into memo field
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0b33175be7d352d9?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 12:06 pm
From: Mike Iacovou

thanks gary.

i'm a noob with access... comfortable with referencing in excel vba... will
get there in the end...

I get an 'unknown type' with the DIM DAO.Recordset - I obviously need to set
some references somewhere... This is a vanilla standard Access install... in
the past have have had inter-system problems based on references - will this
be an issue if I add references in this project (and what am i missing ?)

Appreciate the help... great ;)

Mike

==============================================================================
TOPIC: excel cell value
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/4c9e7db119fdea74?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 12:18 pm
From: jnewl

want to check for a specific value in col 1 and row 1. if that value is not
there, will cancel the operation.

how do i check for that value?

tried this

xlsheet.cells("1:1") = rowvalue

If rowvalue = "code" Then
GoTo contin
Else
GoTo Err_addl_update_Click
End If
contin:

rowvalue is defined as a string

get error msg that not valid construct

thanks for your help

==============================================================================

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

Excel for Life

Rabu, 14 November 2007

25 new messages in 15 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya