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.TableDefs(i).NAME, 3) = "tbl" Then
CurrentDb.TableDefs(i).Properties("Connect") =
constODBC_ConnectString
CurrentDb.TableDefs(i).Properties.Refresh
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.TableDefs(i).NAME, 3) = "tbl" Then
CurrentDb.TableDefs(i).Connect = constODBC_ConnectString
CurrentDb.TableDefs(i).RefreshLink
MsgBox CurrentDb.TableDefs(i).NAME & vbCrLf & vbCrLf &
CurrentDb.TableDefs(i).Connect
End If
Next i
===CODE SNIPPET END===
constODBC_ConnectString 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.
|
| |  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
|
| | 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
|
| | 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_Description, tblCore.c_Submitter, tblCore.c_CloseDate, tblCore.c_LastUpdate, 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.optSearchCriteria.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.SetFocus 'Problem occurs here Run Time Error: cannot move focus to the control txtSearchDump'
Me!txtSearchDump.Text = strSQL
CurrentDb.QueryDefs("qryCoreSearch").SQL = strSQL
Me.lstCoreSearch.Requery
End Sub
|
| | 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
|
| | 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.developersdex.com ***
|
| | 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
|
| | 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.
|
| | 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.
|
| | 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_Click()
Dim varItem As Variant
Dim strSurname As String
Dim strFilter As String
' Build criteria string from lstSurname listbox
For Each varItem In Me.lstSurname.ItemsSelected
strSurname = strSurname & ",'" & Me.lstSurname.ItemData(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 "LabelsEmployeeAddress", acViewPreview
With Reports![LabelsEmployeeAddress]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Malcolm Webb
|
| | 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("Time 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
|
| | 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.ActiveConnection = CurrentProject.Connection
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
|
| | 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
|
| | 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
|
| | 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
|
| | 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_Click
'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\settlement_lett.doc"
End With
----------------------------------
Any help much appreciated.
Thanks in advance
Dawn
|
| | 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.SelectedItem.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 TeacherStudentSubFrm on the Mainform, FirstNameTxt on the TeacherStudentSubFrm, 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_DblClick(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 |
| | 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\Query Wizards\My Query Wizard 0
HKEY_CURRENT_ACCESS_PROFILE\WiВzards\Query Wizards\My Query
Wizard 1 Version 1
HKEY_CURRENT_ACCESS_PROFILE\WiВzards\Query Wizards\My Query
Wizard 1 Library |AccDir\CopyofMy.mda
HKEY_CURRENT_ACCESS_PROFILE\WiВzards\Query Wizards\My Query
Wizard 1 Function LaunchMy
HKEY_CURRENT_ACCESS_PROFILE\WiВzards\Query Wizards\My Query
Wizard 1 Description My Query Wizard
HKEY_CURRENT_ACCESS_PROFILE\WiВzards\Query 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
|
| | 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.
|
| | 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.EggColor)=[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.?
|
| | 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.OpenRecordset(strSQL, dbOpenDynaset)
Set RsttmpSaleDetails = CurrentDb.OpenRecordset("tmptblSaleDetails")
RsttmpSaleDetails.MoveFirst
Do Until RsttmpSaleDetails.EOF
RstSaleDetails.FindFirst "UPC = " & RsttmpSaleDetails!UPC
If RstSaleDetails.NoMatch = True Then
RstSaleDetails.AddNew
RstSaleDetails!SalesID = txtSalesID
RstSaleDetails!UPC = RsttmpSaleDetails!UPC
RstSaleDetails!QtySold = RsttmpSaleDetails!QtySold
RstSaleDetails.Update
Else
RstSaleDetails.Edit
RstSaleDetails!QtySold = RstSaleDetails!QtySold +
RsttmpSaleDetails!QtySold
RstSaleDetails.Update
End If
RsttmpSaleDetails.MoveNext
Loop
|
| | 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
|
| | 7 answers | Add comment |
|
