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
|
| Options |  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
|
| Options | 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
|
| Options | 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
|
| Options | 2 answer | Add comment |
Tuesday, 21 August 2007
|
| Connect property of linked ODBC table Colin Anderson 23:31:05 |
| | 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.
|
| Options | 4 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
|
| Options | 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 |
| Options | 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 |
| Options | 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
|
| Options | 1 answer | Add comment |
Wednesday, 9 May 2007
|
| Export table to TXT file using 2 methods Neil 00:39:44 |
| | Hi guyz, just trying out this google feature so if i post if in the
wrong area i appologize. now to my question. BTW i'm new to access
programming, i've done a little vb6.0 and vb.net but access vba is a
little confusing for me since i have never used it plus i have lost
touch with vb coding since last 8 monhts.
anywayz, my problem is i'm trying to export a table that's modified by
me which is taken from a file at a prompt, now i have to export the
same file in the same format it came in Fixed and Delimited formats. I
can do this easily using the DoCmd.TransferText acExportDelim, "ORDER
Export Specification", "TBL0219", "C:\Test.txt" . All i do is change
the acExport to Fixed.
The file being imported has fixed width properties. It has 2 types of
records M(main set of record i.e Cust name, ph, email etc) and S(if the
operator upsold items it adds these records w/ corresponding field).
Once it is modified it now has just main record all M's. Now exporting
this format is easy but due to constraints i have to have the new
database output to the same format it used to be in which mean i have
to check now which one of the fields now has additional upsale items
i.e added QTY and PartNo.
Any ideas on how i can work this?
Example
1. Raw input file looks like
MTea 20050218 James Connor 1 1 1 3005 2003
4003 etc
MBag 20050219 Joanne Smith 1 1 1 3005 2003
4003 etc
S 1 3009
2. After my modification it looks like
MTea 20050218 James Connor 1 1 1 3005 2003
4003 etc
MBag 20050219 Joanne Smith 1 1 1 3005 2003
4003 etc 1 3009
3. Output needs to look like raw data again
Any help will be apprecaited
thanks Neil
|
| Options | 7 answers | 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.
|
| Options | 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.?
|
| Options | 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
|
| Options | 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
|
| Options | 7 answers | Add comment |
Wednesday, 29 November 2006
|
| Replacing characters John 14:11:11 |
| | Hi
How can I replace characters like carriage return and double quotes from
strings?
Thanks
Regards
|
| Options | 9 answers | Add comment |
Tuesday, 31 October 2006
|
| Finding duplicates Maxi 09:14:32 |
| | I have 100 tabes in an Access database, every table has 1 filed with
100 names (records), no primary key assigned. I would like to find
duplicates.
Here is the criteria:
The computer should pick up the first name of Table1 and check that
name in that table (Table1) as well as the remaining 99 tables.
Continue this till we reach the last name (record) of the 100th table.
Display the result in another table/query.
What is the best way to accomplish this task and how to do it.
Maxi
|
| Options | 16 answers | Add comment |
Saturday, 21 October 2006
|
| Count query dBNovice 01:23:23 |
| | Hey group,
I am trying to do a count of the number of papers in a table. The
table has a PaperID that differentiates each paper , e.g. 004.1. Some
papers are reused. The reused paper is given a new PaperID. The
PaperID includes 3 new numbers appended to the original PaperID, e.g.
664.004.1.
When I do a count, I do not want to count the reused paper. I set up a
count query and had the criteria { Not Like "***.***.*" }. I have also
tried the criteria { Not Like "###.###.#" } ,
{ Like "***.*" } and { Like "###.#" } but neither gives me the correct
count. Please help!!!
|
| Options | 9 answers | Add comment |
Saturday, 9 September 2006
|
| Select Option Guest 09:07:59 |
| | I have a form (frmSelectIndustry) that I use to determine which filter
to apply to a forms recordset. I want to reuse this form for other
parts of my application and I am trying to rewrite the code so that it
can have more than one use. Currently I store selected items from a
list box into a string and then open a form (frmVendorsMenu) with the
string as a filter.
The problem with this is that the form (frmSelectIndustry) can only be
used to open one form (frmVendorsMenu) but I would like to reuse the
form so that it could be used again in the application for example open
another form, use a query criteria etc...
Can anyone suggest a way of acheiving this?
TIA
|
| Options | 4 answer | Add comment |
Thursday, 31 August 2006
|
| Mouse Wheel Loose First Record Jv 20:26:41 |
| | I have quite a few of continuous form and subform where I do allow
scroll bars. I run into problems with the mouse wheel whenever the
data on the form does not take up the whole page. In this instance,
whenever a user uses the mouse wheel to scroll down, the first record
disappears off the screen and they can't get it back unless they click
on the Refresh button or Page Up. The mouse wheel never scroll back up
to the first record. However, this problem does not occur if the data
takes up the whole page.
Does any one know how to fix this problem without disabling scroll bars
and mouse wheel?
Thanks.
Julie Vazquez
|
| Options | 2 answer | Add comment |
|
| Database Update Vibhu 16:22:03 |
| | Hello all,
I am using ADO.NET and trying to update the MsAccess database but
unable to do so.
Here is the code below that I used.
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim sql As String
Connect()
sql = "select key, title from tblISBN"
ds = New DataSet
da = New OleDbDataAdapter(sql, conn)
da.Fill(ds, "tblISBN")
TextBox1.DataBindings.Add("Text", ds, "tblISBN.title")
TextBox2.DataBindings.Add("Text", ds, "tblISBN.key")
ds.Tables(0).Constraints.Add("PK", ds.Tables(0).Columns(0),
True)
End Sub
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSave.Click
Dim cb As OleDbCommandBuilder
cb = New OleDbCommandBuilder(da)
Try
da.UpdateCommand = cb.GetUpdateCommand
da.Update(ds, "tblISBN")
Me.BindingContext(ds, "tblISBN").EndCurrentEdit()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
It updates the database but when I check the actual msaccess database
file, the data is not updated in that.
Any help will be beneficial.
Thanking You,
Regards,
Vibhu.
|
| Options | 4 answer | Add comment |
Thursday, 24 August 2006
|
| Clear filters and save doesn't stick. Guest 19:16:42 |
| | I'm trying to defeat persistant filters with the following code in
Close, and also in Unload:
Private Sub Form_Close()
DoCmd.RunCommand acCmdRemoveFilterSort
Me.FilterOn = False
Me.OrderByOn = False
Me.ServerFilterByForm = False
My.OrderBy = ""
Me.Filter = ""
Me.ServerFilter = ""
DoCmd.Save acForm, Me.Name
DoCmd.RunCommand acCmdSave
End Sub
Somehow the user applied filters stills get saved. I'm not sure what
else to do.
|
| Options | 2 answer | Add comment |
Monday, 14 August 2006
|
| Error message on only one computer in network Guest 12:19:44 |
| | 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.
|
| Options | 4 answer | Add comment |
|
| Switching between Access and Excel using code Garry Oxnard 11:50:21 |
| | Can anyone help me to solve a problem which involves switching from
Access to Excel (then back to Access) programatically please?
I have an Excel template which, on open, also opens an Access database
- containing a list of addresses.
Sequence of events is =
(1) Excel template opens in its default XXX.xls filename.
(2) Code runs to save the spreadsheet as XXX.xls.
(3) User clicks a button to open an Access database containing an
address list.
(4) When Access opens, a form generates a unique number and the OnOpen
event fires the unique reference number to a cell in the XXX.xls
spreadsheet.
(5) [[[[User has to click Excel on the taskbar to restore the
spreadsheet.]]]]
(6) A message box with an OK button opens in the spreadsheet and when
user clicks the button, code runs to save the spreadsheet using the
unique ref as a filename (e.g 3478.xls).
(7) [[[[User must then click Access on the taskbar to restore the
database. He can then choose an address and click the ok button. This
fires the address into the now saved spreadsheet (e.g 3478.xls,]]]]]
(8) Access closes and the spreadsheet remains open.
Note - the bits above in square brackets are the bits I can't make work
(ie automating the movement back and forth between the applications).
Although this seems convoluted, the only way I can fire data from
Access to Excel is by saving the spreadsheet in a default name first -
so that Access has a path to which it can then fire the ref number
(otherwise it crashes). I need the user to switch to Excel in order to
make it active, then re-save the sheet using the ref number as a file
name. The user must then go back to Access to choose the address.
Problem is that I can't make the code work to switch the user
automatically from Access to Excel, then back again. The rest of the
code works ok if I click the applications on the taskbar manually. My
users will not know to do this.
I found a posting on Google from Ed Wagner - which sounds as if it will
do exactly the job I need (post now closed) but the code is beyond me.
I include the block below in the hope that someone can give me a bit of
help. I really need to know where to place the code in order to make it
work. If it needs to be called - how do I do this please? If bits are
missing (e,g End Function) I'd much appreciate it if you could let me
know.
Here is the relevant part of Ed's posting (I haven't missed any of his
code out - if there are bits missing he missed them out): Many thanks
for your possible help.
Garry.
"As far as switching between apps goes, it is necessary to have Windows
do this
for you via the API. Windows keeps track of open windows by assigning
each one
a handle. This is a long integer that uniquely identifies the window.
The
following declaration and statement will obtain the handle for a
specific class
of window":
Private Declare Function FindWindow Lib "USER32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
lngHwnd = FindWindow("MSACCESS", vbNullString) 'for finding Access
or
lngHwnd = FindWindow("XLMAIN", vbNullString) 'for finding Excel
"The variable lngHwnd will then be assigned the handle to the window.
If the
specified window is not found, lngHwnd will be given a value of zero.
The
following declaration and statement will then set the focus to the
specified
window: "
Private Declare Sub SetFocus Lib "USER32" _
(byval hwnd as long)
SetFocus lngHwnd
|
| Options | 1 answer | Add comment |
|
| Access 97 working slowly...??? Korpisoturi 11:04:36 |
| | Access 97 working little slowly. I have windows 2000. The computer is
old..processor is about 300 MHz and RAM- memory capasity is ~164 Mb.
Hard disk is 3.0GB.
I have only three tables which are similar. In a table is two fields
"date" and "barcode".
I am use MSComm - object, I receive the barcodes from virtual com -
ports. When the data incoming to COM port I calling "AddScanRecords".
The code is same in everyform.
Access 97 working little slowly. I have windows 2000. The computer is
old..processor is about 300 MHz and RAM- memory capasity is ~164 Mb.
Hard disk is 3.0GB.
I have only three tables which are similar. In a table is two fields
"date" and "barcode".
I am use MSComm - object, I receive the barcodes from virtual com -
ports. When the data incoming to COM port the code on below
opening...The code is same in every form.
Private Sub MSComm1_OnComm()
Dim buffer As String
Dim T1 As Single
buffer = MSComm1.CommEvent
T1 = timer
Do Until buffer = 2
If timer - T1 > 2 Then Exit Sub
Loop
Call AddScanRecords
End Sub
Sub AddScanRecords()
Dim db As Database, rs As DAO.Recordset
Dim StartTime As Date
StartTime = Now()
Set db = CurrentDb
Set rs = db.OpenRecordset("Table1")
rs.AddNew
rs![BarCode] = MSComm1.Input
rs![ScanDate] = Now()
rs.Update
rs.Close
db.Close
End Sub
Is the database slow because I don't use asynchronous method to open
the database?
How can I open the database using asynchronous method?
Please help me!
|
| Options | Add comment |
|
| MS Access 2000 VBA Code Shows Totally Incorrect Date from Controls on a Form Easystart 09:38:36 |
| | Hi,
Sorry for my English. English is not my native tougue.
I am working in MS Access 2000 with a SQLServer 2000 Backend database.
MS Access 2000 is my GUI front end that has SQLServer linked tables in
it. One of my forms has two TEXT BOX controls formated as Short Date.
The form is binded to a linked table. The linked tables has about 7
records and one of the control is binded to a table field. These two
controls displays the date correctly as 01/07/2006 and 31/07/2006 (I am
running Windows XP, my regional setting is set to English Australia,
location is set to Australia, language for non-Unicode programs is set
to English Australia, and my date format is set to d/MM/YYYY) when the
form is running. I have some VBA code behind the form to print out
these two dates to a text file. The dates show up as 30/12/2005 and
30/01/2006. I really do not know why these dates are totally different.
Here's my VBA code:
Open "C:\Results.txt" For Output Access Write Lock Write As #1
Print #1, "GL run from "; Me.txtFromDate; " to "; Me.txtToDate
Print #1, ""
::
::
Close #1
I am hoping some one has come across with this problem.
Thanks in advance,
Easystart
|
| Options | 5 answers | Add comment |
|
