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 |
|