How to learn who reads my blog?
MS Access database development
Hello Guest
  
  • Login
• Register…
• Start blog
  • Who, Where, When
• What is interesting here?
• Duels
  • Polls
• Avatars
• Interests
  • Cities and Countries
• Random blog
• Users search
  • Search
• Games
• Tests
• QAIX
  • Сообщества
• Talxy Chat
• Horoscope
• Online
 
Register!

QAIX > MS Access database developmentGo to page: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | next »

  Top users: 
  Recent blog posts: 
  They have birthday today: 
  Forums:   
  Discuss: 
  Recent forum topics: 
  Recent forum comments:
  Модератор:
Yesterday — Monday, 8 February 2010
Connect property of linked ODBC table Colin Anderson 18:24:19
 Hi

I am trying to refresh table links at startup in an Access 2002
application. The following code is based on a number of articles
posted in this group but this does not seem to work:

===CODE SNIPPET START===
For i = 0 To CurrentDb.TableDefs­.Count - 1

If Left(CurrentDb.Tabl­eDefs(i).NAME, 3) = "tbl" Then

CurrentDb.TableDefs­(i).Properties("Conn­ect") =
constODBC_ConnectSt­ring
CurrentDb.TableDefs­(i).Properties.Refre­sh
CurrentDb.TableDefs­(i).RefreshLink

MsgBox CurrentDb.TableDefs­(i).NAME & vbCrLf & vbCrLf &
CurrentDb.TableDefs­(i).Connect

End If

Next i
===CODE SNIPPET END===

I also tried:

===CODE SNIPPET START===
For i = 0 To CurrentDb.TableDefs­.Count - 1

If Left(CurrentDb.Tabl­eDefs(i).NAME, 3) = "tbl" Then

CurrentDb.TableDefs­(i).Connect = constODBC_ConnectSt­ring
CurrentDb.TableDefs­(i).RefreshLink

MsgBox CurrentDb.TableDefs­(i).NAME & vbCrLf & vbCrLf &
CurrentDb.TableDefs­(i).Connect

End If

Next i
===CODE SNIPPET END===


constODBC_ConnectSt­ring is a global string variable containing a valid
connect string.

No errors are generated but the Connect property does not get updated
in either case. The original (manually linked) connect property is
maintained.

Please help.

comment 5 answers | Add comment
Wednesday, 23 December 2009
Access 97 - Multiuser databases and corruption Rob Geraghty 16:12:31
 I've just spent some time looking through FAQ sites and searching the
google archives of this newsgroup, but I still haven't been able to
find a clear explanation of an issue with multi-user databases.

Essentially I have two questions;

1) Does the system.mdw file have any significance to multi-user
sharing of an Access 97 database other than security?

2) Can any number of users open an Access 97 database using the same
account from the system.mdw without corrupting the database?

What I have been doing up until now is trying to ensure that each user
has a unique account in the system.mdw and that any user connecting to
the database uses the same system.mdw. If it's not necessary for each
user to have a unique account, it would be much simpler to have a
single generic user account and a single standard desktop shortcut to
the front end.

I can't find any resources which clearly explain the locking system in
Access. My impression is that when a user connects to the database,
an LDB file is created which contains the details of the connection.
The information stored in the LDB file as shown by the Microsoft LDB
viewer tool appears to be related to the computer name making the
connection *not* the user account from the system.mdw, so presumably
it shouldn't matter if the same account is used on several different
computers because the locking is unique to the PC name.

What *does* seem to cause problems is failing to use a system.mdw at
all; which means that several users of an unsecured database are
connecting as "admin" from the system.mdw in c:\windows\system32­. In
terms of rights to objects, security is not of particular importance
in the databases in question.

Having standard desktop shortcuts which include the login account as
part of the command line would make distribution of the applications
much simpler.

If anyone has in depth knowledge of how the system.mdw file and the
ldb files behave, please let me know whether the setup for these
databases can be simplified...

Rob
comment 16 answers | Add comment
Tuesday, 12 May 2009
How to calculate the sum of a colon Ladislau S. 04:12:30
 Dear reader,

I am an occasional user of MS Access 2000 running on Windows 98. My hobby is
ship model building so I made a database for things that I want to buy.
After two strokes I bin unable to remember or find how to calculate the sum
of the prices of a column. I made query to calculate the price of itch item
(I have some multiple items). Whit this query I made a nice report, bat the
best, how match is the price of my wish list I do not am the opportunity to
now. This time it is very hard for my to calculate, to write or to speak.
I would like to thank you in advance for your precious help which can gave
my in ending this project.
Sincerely yours,
Ladi


comment 2 answer | Add comment
Monday, 10 November 2008
I have a problem in a setfocus comm... Guest 15:26:29
 I have a problem in a setfocus command. Can any1 help? Any advice is much appreciated

Private Sub btnFind_Click()
Dim strSQL, strSelect, strFrom, strTitle, strDescription, strSubmitter, strOwner As String

Set db = CurrentDb

strSelect = ("SELECT tblCore.[c_ID], tblCore.c_Title, tblCore.c_Descripti­on, tblCore.c_Submitter­, tblCore.c_CloseDate­, tblCore.c_LastUpdat­e, tblCore.c_Owner ")
strFrom = ("FROM tblCore ")

strTitle = ("WHERE (((tblCore.c_Title)­Like " & Chr(34) & "*" & Chr(34) & "&" & Chr(34) & [Forms]![frmCoreMain]![txtSearch] & Chr(34) & "&" & Chr(34) & "*" & Chr(34) & "));")

********** Continued

Select Case Me.optSearchCriteri­a.Value
Case 1
strSQL = strSelect & strFrom & strTitle
Case 2
strSQL = strSelect & strFrom & strDescription
Case 3
strSQL = strSelect & strFrom & strSubmitter
Case 4
strSQL = strSelect & strFrom & strOwner
End Select

Me!txtSearchDump.Se­tFocus 'Problem occurs here Run Time Error: cannot move focus to the control txtSearchDump'
Me!txtSearchDump.Te­xt = strSQL
CurrentDb.QueryDefs­("qryCoreSearch").SQ­L = strSQL
Me.lstCoreSearch.Re­query
End Sub
comment 1 answer | Add comment
Friday, 31 October 2008
Access beginner User..Need help setting upa database Shani 13:30:32
 I need help setting up a database which includes a list of about 1200
urls. Anyone who accesses the database should be able to input wheather
the url is good or bad. I want to share the databse over a network. If
possbile I would like a web page view with Url displayed at the top of
the page, the actual URL content displayed in the middle, and the
option to input weather or not the URL is good or Bad. I would
appreciate it if someone can help set this up

comment 5 answers | Add comment
Monday, 22 September 2008
Check new report is already exists in CurrentDb. Guest 01:36:09
 I am currently using MSAccess 97.
I created a new report from coding. I want to know this report's name is already exists or not in CurrentDatabase.
Add comment
Thursday, 4 September 2008
Sum IF in Access Tee GEE 11:47:18
 Is there a SumIF function in Access? I have to SUM a duration of time,
but only if a logical Yes/No check box is TRUE. I am working with the
expression builder and a Text Box. Thanks for the help.

Forgive me...I'm a beginner programmer with expert expectations.

*** Sent via Developersdex http://www.develope­rsdex.com ***
comment 5 answers | Add comment
Friday, 11 July 2008
Current event fires twice - how to excute code just once? Guest 02:35:16
 I'm surprised that the Current event of forms always fires twice
(Access XP).
I have quite a lot of processing done in the current event, how to make
the code run just once?

Thanks

comment 4 answer | Add comment
Wednesday, 18 June 2008
The data has been changed. Another user edited this record and saved the changes before you attempte Daniel 08:27:54
 Hi All,

I have a form and VB code in Access. The code is like


With rs
.Edit
!Field = 10
.Update
End With

When I chang a value on the form. then the error shows up.

The data has been changed. Another user edited this record and saved
the changes before you attempted to save your changes. Re-edit the
record

Anyone has idea what cause that? Thanks in advance.

comment 13 answers | Add comment
Tuesday, 20 May 2008
Error message on only one computer in network Guest 05:42:07
 We use several databases on several computers in a small network, and
on a new computer we just bought we get the following message every
time we try to activate any procedure:
"The expression (OnOpen, or OnClick, whatever generated the sub) you
entered as the event property setting produced the following error: A
problem occured while (the name of my database) was communicating with
the OLE server or ActiveX control."
The standard help options of Access do not solve the problem.

comment 6 answers | Add comment
Thursday, 17 April 2008
Mailing Labels Malcolm Webb 01:37:02
 I need to produce mailing labels every week for staff, but not always for
the same people. I have found a procedure using a filter form which
allows individual names to be selected from a list box which are then
applied to a report for printing.

I have an Employees Table filtered via a query to only show current staff.

My FilterForm is tied to this query and the list box on the filter form
shows surname and forename. I can choose any number of names I require
then click an ApplyFilter command button and my chosen names appear on the
Label Report. Just waht I want.

BUT -- next time I open the filter form there is a name missing. The top
name is missing and when I check the employees table the surname has been
deleted, although all the other information is there. I can easily put it
back. But next time it is deleted again. And if I don't put the first
surname back the next one is deleted.

Can anyone see anything amiss with the following code which would cause
the top surname in the list to be deleted?

Many thanks.

Malcolm Webb


Private Sub btnApplyFilter_Clic­k()
Dim varItem As Variant
Dim strSurname As String
Dim strFilter As String

' Build criteria string from lstSurname listbox
For Each varItem In Me.lstSurname.Items­Selected
strSurname = strSurname & ",'" & Me.lstSurname.ItemD­ata(varItem) _
& "'"
Next varItem
If Len(strSurname) = 0 Then
strSurname = "Like '*'"
Else
strSurname = Right(strSurname, Len(strSurname) - 1)
strSurname = "IN(" & strSurname & ")"
End If

' Build filter string
strFilter = "[Surname:] " & strSurname

' Open the mailing label report and apply the filter.
DoCmd.OpenReport "LabelsEmployeeAddr­ess", acViewPreview
With Reports![LabelsEmployeeAddress]
.Filter = strFilter
.FilterOn = True
End With
End Sub


Malcolm Webb

comment 2 answer | Add comment
Friday, 14 March 2008
GetRows Hank 19:50:55
 Hello,
I'm having trouble reading from a table directly into an
array using Access 2000.

Dim db As Database
Dim rsTime As Recordset
Dim TimeArray As Variant

Set db = CurrentDb
Set rsTime = db.OpenRecordset("T­ime Record")
' Only load array if records exist
If (rsTime.RecordCount­ > 0) Then
rsTime.MoveLast: rsTime.MoveFirst
' The record count = 10
TimeArray = rsTime.GetRows
End If
It only appears to load the first record. I see only row 0
vResult = TimeArray(0,0) results in good data
vResult = TimeArray(0,1) gives me a "Subscript out of Range"
error

Any ideas would be appreciated.
Hank Reed

comment 34 answer | Add comment
Tuesday, 26 February 2008
Transfertext with ADO Recordset created in Runtime Guest 11:04:53
 Hi

I'm trying to export an ADO Recordset generated at Runtime to a txt
file.

I know that you have two options for exporting Access information:
1) TransferText
2) Reading / Writing Text Files

Does Transfertext allow usage of Recordsets as source of information?
It works with tables and queries defined in Access DB but Can I use a
recordset generated in runtime?

The following code fails in the trasnfertext line...

Sub ExportTXTfile()
Dim rslt As ADODB.Recordset
Dim strSQl As String
strSQl = "SELECT * FROM [FBW-OrgLoad] WHERE [MAU]='3514'"
Set rslt = New ADODB.Recordset
Set rslt.ActiveConnecti­on = CurrentProject.Conn­ection
rslt.CursorType = adOpenStatic
rslt.Open strSQl
MsgBox rslt.RecordCount

**** Recordset is created in runtime and works.

DoCmd.TransferText acExportDelim, "", rslt, "C:\TEST3514.txt"
End Sub

Regards
LCK

comment 3 answer | Add comment
Wednesday, 12 December 2007
Differences between Access 97 and Access 2000/2003 John Ortt 17:11:16
 Hi All,

Our company is migrating from Windows NT to XP and in the process from
Access 97 to Access 2003.
Due to the phased rollout we are faced with a situation where some users are
still on NT while others have been fully upgraded, but all users need to use
the same Access databases.
At first we tried just opening the Access 97 databases with XP but we were
worried that some of the less computer-literate employees may click "yes" on
the "Do you want to convert this database to the latest version" prompt.
The solution we have come up with is to create an exact duplicate of the
database in XP but with all the Tables removed and relinked to the 97
version.
This solution is working well on the whole but there have been a couple of
instances where that database has required a repair.

The main problem we are still having is that my colleagues and I can no
longer make any changes to the database while there are any users in. Is
this a setting which we can change?
For instance, on 97 you could alter a form in the live database and then
save the altered form......XP doesn't seem to allow you to do that.

Any help would be massively appreciated.

Thanks in Advance,

John


comment 9 answers | Add comment
Monday, 19 November 2007
Deleting Tables Nick 'The database Guy 04:28:45
 Hello People,

Does anyone know how to browse and delete folders in VBA?

Thanks,

Nick McMillen

comment 5 answers | Add comment
Tuesday, 11 September 2007
DoCmd.OpenReport "rpt1", acViewNormal - Choose Printer Dialog Tim Marshall 05:56:52
 A2003

Most of the time, in past apps, my report routines are usually a preview
first and then DoCmd.RunCommand acCmdPrint for the printer dialog.

However, I'm now faced with wanting to send a report to a printer of
choice withoutthe preview. I'm aware of Albert Kallal's snippet of code
that allows you to change the default printer, but I'm wondering if
there's any other way?

TIA
--
Tim http://www.ucs.mun.­ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
comment 2 answer | Add comment
Tuesday, 7 August 2007
Opening a mail doc from Access - code problem Andy Davis 20:33:55
 Hi

I've written the following code for a button on a form to open a mail merge
document in MS Word. The code opens the document fine but does not display
the usual connection details to open the table in my database when the file
is opened.i.e:
"Opening this document will run the following SQL command:
SELECT * FROM 'filename' "
Hence all that is opened is a document with no updated details from the
source table. If I open the document directly in Word it works OK.

-------------------­----------------
VBA Code in Access form:

On Error GoTo Err_SettLett_Word_C­lick
'declare variables
'object variable that refers to Word
Dim appWord As Object

'Word.Application data type established from reference to Word's object
library
Set appWord = CreateObject("Word.­Application")
'make Word visible
appWord.Visible = True

'Open Word document
With appWord
.Documents.Add "C:\DD\Database work\GCRE\settlemen­t_lett.doc"
End With

-------------------­---------------

Any help much appreciated.

Thanks in advance

Dawn


comment 3 answer | Add comment
Saturday, 23 June 2007
I want to have the ID selected from... Guest 07:38:40
 I want to have the ID selected from the listview of one form to the textbox of another form
where txtcontactid is the textbox &strcontactid is taking the selected value but on running this error is coming "No value given for one or more parameters"
what I am doin is On the form load of form2 I have written this querry

Dim strContactId As String
strContactId = Search.ListView1.Se­lectedItem.SubItems(­1)


rs.Open "select * from contactinformation where txtContactid=" & strContactId, connection, adOpenDynamic, adLockOptimistic
Add comment
Saturday, 16 June 2007
I need code to open TeacherDetailFrm. Guest 19:05:55
 Hi
I need code to open TeacherDetailFrm.

I have TeacherStudentSubFr­m on the Mainform, FirstNameTxt on the TeacherStudentSubFr­m, and TeacherDetailFrm.
I need code to open the TeacherDetailFrm By double click on the FirstNameTxt.

I use this code but no data come:

Private Sub FirstNameTxt_DblCli­ck(Cancel As Integer)
Dim DocSubForm As String
Dim DocText As String
DocSubForm = "TeacherDetailFrm"
'DocText = "[Result]=" & "'" & Me![result] & "'"
DoCmd.OpenForm DocSubForm, , , "[ FirstNameFld ]=forms![TeacherStudentSubFrm]![ FirstNameTxt]"

End Sub
comment 1 answer | Add comment
Monday, 21 May 2007
Problem with Add-in Ap 08:09:19
 I have build an addin that works great as a menu add in, however I
recently tried to make it a query wizard so it would appear in the
options for a new query. It shows up in the new object box, but causes
Access 2K to crash when I select it. I believe I have configured the
USYSREGINFO table properly, but I am not positive. See below:


Subkey Type ValName Value
HKEY_CURRENT_ACCESS­_PROFILE\WiВzards\Qu­ery Wizards\My Query Wizard 0
HKEY_CURRENT_ACCESS­_PROFILE\WiВzards\Qu­ery Wizards\My Query
Wizard 1 Version 1
HKEY_CURRENT_ACCESS­_PROFILE\WiВzards\Qu­ery Wizards\My Query
Wizard 1 Library |AccDir\CopyofMy.md­a
HKEY_CURRENT_ACCESS­_PROFILE\WiВzards\Qu­ery Wizards\My Query
Wizard 1 Function LaunchMy
HKEY_CURRENT_ACCESS­_PROFILE\WiВzards\Qu­ery Wizards\My Query
Wizard 1 Description My Query Wizard
HKEY_CURRENT_ACCESS­_PROFILE\WiВzards\Qu­ery Wizards\My Query
Wizard 4 Index 0


I assume the problem is not with the table because it seems to install
properly, it just does not want to run the function LaunchMy. Any ideas

why an add in would work as a menu add in and fail as a wizard add in?


Reply

comment 1 answer | Add comment
Friday, 4 May 2007
Dcount -> SQL Sheldon Mopes 07:28:32
 I posted this about a week ago, and I got some interesting responses,
but none of them acually answered what I was asking, so I'll try
again..

I have read a few articles that state that a multi-user app over a
network will run faster if DSum & DCount functions are replaced with
SQL statements replicating the functions.What would be a SQL statement
that would be functionally equal to DCount ? As I am a novice to SQL,
anysuggestions would be hugely appreciated. Thanks.

comment 6 answers | Add comment
Sunday, 15 April 2007
Access 2000 upsized to SQL Server problem passing value from Form to Query Guest 18:00:29
 I used the upsize wizard to change my Access 2002 database to a SQL
Server ADP project. I had a form where the user entered a value into a
text box and when a command button on the form was clicked a Report was
opened. The reports record source is a query. The query uses the
value from the form text box to restrict the query.
Table name = EggsTable
one of the columns in the table is named: EggColor
Form name = EggColorForm
Form text box name = ColorTextBox
the OLD sql query is:
SELECT EggsTable.EggColor
FROM EggsTable
WHERE (((EggsTable.EggCol­or)=[Forms]![EggColorForm]![ColorTextBox]));

This no longer works. Does anyone know how to change the syntax to get
this to work? Or do I need to do something different, like function or
variable, etc.?

comment 1 answer | Add comment
Saturday, 24 February 2007
Data Type Mismatch Jake 23:41:45
 I am currently trying to create my own Point Of Sale software for my
retail store. I wrote the program with the UPC field as Long integer.
When I started to add the products by UPC code, I got a data mismatch
error. I realized UPC codes are 12 digits, sometimes more and
sometimes less. This falls out of the Long integer field type. I then
changed my type on all UPC fields in all of the tables containing this
field to a text data type. This would allow me to enter the UPC codes
in; however, after doing this much of my code gave me data type
mismatch errors. I get this error when using the Find method to update
my inventory table to reflect the changes made to the inventory from
the sale transaction. I also get this error when I try using a SQL in
my code to select from the Inventory table the record that matches the
UPC of the sale. Does anybody have any suggestions? Here is some of
the code I'm having problems with:

Dim RstSaleDetails As DAO.Recordset
Dim RsttmpSaleDetails As DAO.Recordset
Dim RstDailySalesReport­ As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblSalesDetails WHERE SalesID = " & txtSalesID
Set RstSaleDetails = CurrentDb.OpenRecor­dset(strSQL, dbOpenDynaset)
Set RsttmpSaleDetails = CurrentDb.OpenRecor­dset("tmptblSaleDeta­ils")

RsttmpSaleDetails.M­oveFirst

Do Until RsttmpSaleDetails.E­OF
RstSaleDetails.Find­First "UPC = " & RsttmpSaleDetails!U­PC
If RstSaleDetails.NoMa­tch = True Then
RstSaleDetails.AddN­ew
RstSaleDetails!Sale­sID = txtSalesID
RstSaleDetails!UPC = RsttmpSaleDetails!U­PC
RstSaleDetails!QtyS­old = RsttmpSaleDetails!Q­tySold
RstSaleDetails.Upda­te
Else
RstSaleDetails.Edit­
RstSaleDetails!QtyS­old = RstSaleDetails!QtyS­old +
RsttmpSaleDetails!Q­tySold
RstSaleDetails.Upda­te
End If

RsttmpSaleDetails.M­oveNext

Loop

comment 5 answers | Add comment
Sunday, 10 December 2006
Splitting string John 22:27:22
 Hi

Is there a way to split a multi-line address field into individual lines of
address in access 97?

Thanks

Regards



comment 7 answers | Add comment

Add new topic:

How:  Register )
 
Login:   Password:   
Comments by: Premoderation:
Topic:
  
 
Пожалуйста, относитесь к собеседникам уважительно, не используйте нецензурные слова, не злоупотребляйте заглавными буквами, не публикуйте рекламу и объявления о купле/продаже, а также материалы нарушающие сетевой этикет или законы РФ. Ваш ip-адрес записывается.


QAIX > MS Access database developmentGo to page: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | next »

see also:
How can I get line number of a Perl…
Re: MSaccess Module
pass tests:
see also:
dfvfe
How to change iPhone Ringtone
ha-ha ._.I have blog and there...

  Copyright © 2001—2010 QAIX
Идея: Монашёв Михаил.
Авторами текстов, изображений и видео, размещённых на этой странице, являются пользователи сайта.
See Help and FAQ in the community support.qaix.com.
Write in the community about the bugs you have noticedbugs.qaix.com.
Write your offers and comments in the communities suggest.qaix.com.
Information for parents.
Пишите нам на .
If you would like to report an abuse of our service, such as a spam message, please .
Если Вы хотите пожаловаться на содержимое этой страницы, пожалуйста .