Modules & VBA :: Get Filtered / Grouped External Data Into MS Access Table
Oct 20, 2014
I have an Excel file with a name range "DBIAS" which identifies all database data.
Then I have an Access file with a form to import that database (better, that named range) into an Access table. While importing, I have to filter some records or grouping by some field.
I cannot run correctly a VBA code to get data (filtered and/or grouped) from that name range and save those records to an existing or a brand new table.
I could get those data as DAO.recordset and printed out with "Debug.print" on immediate window, but I cannot complete the final step: writing those records to a table.
View Replies
ADVERTISEMENT
Jul 20, 2015
I'm very new to Access and I'm attempting to write an expression in a query that will calculate the % of the count of "Exchange" field (Exchange is a text field and is grouped and the count based on each unique name) where the total count is based on the filter where "Group" = 'FS' or 'S'.
The below seems to work, but there is a better way of going about this (especially if I have to add more filter criteria). I added a pic of the query I'm trying to build.
PercentofTotal: (Count([Exchange])/DCount(Count([Exchange]),"[Holdings]","[Asset Group] = 'FS' OR [Asset Group]='S'"))
View 11 Replies
View Related
Feb 8, 2014
I am asking my users to interact with the Get External Data dialog, and I'm trying to restrict what they can do.
Code:
DoCmd.RunCommand acCmdImportAttachText
I really like the mapping feature Access uses so I want to use that part of the Wizard, but I don't want my users to import data to the wrong table.Can I skip the first page of the 'Get External Data' Dialog series? Invoke the 'Import Text Wizard' without the previous dialog?see: AWF2. I can get the file path from a File Picker dialog.
Can I set the combobox to a default selection?
View 3 Replies
View Related
Apr 14, 2014
I have a large database with over a million entries. I am trying to filter certain years (04-12) and certain states (western US). After the two filters are on my entries are down to 328,000. Now, I want to save the 328,000 to a new database.
View 1 Replies
View Related
Oct 7, 2013
I have a split form with graphs in the upper design section and the table of the data that the graphs represent in the data view underneath. I would like to make the graphs dynamic with the data from the forms if the data is filtered in design view.
I know how to get the filter from the data view by using the .filter and I would like to use that as part of an SQL statement in my Rowsource for the graphs to dynamically change the graphs when a filter is used. My problem is the data is returned with .filter function returns with quotation marks (example below), and because I'm wanting to use that data in a string to change my rowsource the quotes need to be replaced with an apostrophe. How can I change the quotes to an apostrophe, or is there a better way to "filter" a graph?
Example:
Returned from .filter
([FrmTable].[CurrentABCS]="A")
What I need:
([FrmTable].[CurrentABCS]='A')
View 2 Replies
View Related
Jul 8, 2015
I am currently working on a form that exports a query that changes after a user set filter is applied, and am able to get the filter to apply and the query to export. The problem I am having is that the worksheet that the query is copied into retains all previous data, and if the earlier query export included more records, they remain as they were, is there any way of getting them to be blank.
I want to export my query onto a worksheet that has current data, need to delete current data or delete worksheet so that only the selected data is shown.
My code currently is:
Private Sub Command67_Click()
Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT NomT.shkFirstName, NomT.shkSurName, NomT.shkCompanyName, NomT.shkAdd1, NomT.shkAdd2, NomT.shkPostCode, NomT.shkRegion, NomT.shkCountry, NomT.shkAdd3" & " FROM NomT" & vbCrLf
With Me.FilterSub.Form
[Code] .....
View 4 Replies
View Related
Oct 30, 2006
I have a database that contains a series of tables & queries that feed a formatted Excel sheet(s). The problem is it is not very portable.
It works fine on my local computer, but if I give the database to someone else and when the open the Excel template and try to refresh the data from the database they get an error "could not find file C:documents and settingsusername...
If I make a file for them off of documents and settings with my username and put the database in it, it works fine.
So I guess the question is, how do I change the path in Excel to reference the users computer without re-doing all the external queries?
View 1 Replies
View Related
Feb 2, 2007
Hi There,
I have recently upgraded from Office XP to Office 2007. I had an Access database which worked fine in Access XP. In it I had a table linked to an Excel spreadsheet. When I entered data on an access form, it updated the Excel spreadsheet [source document] accordingly. This does not seem to work in Access 2007. The linked table option now does not permit data entry or amendement. The other "Get external data" options create a table in Access which also does not update my source Excel document. The idea is to enter data in Access using a form. This data is placed in an excel spreadsheet. Excel gets some lookup values, and then enters these in a Word mail merge. Without the functionality of a linked table and data entry, I cannot produce new reports.
Can anybody help please?
Regards
Mark M.
View 2 Replies
View Related
Jan 23, 2015
code that will allow me to take data [All Fields] from a Table in my Database and Insert them into another identically structured Table in another MS Access accdb Database.I would also like to filter for records older than a month [MyDateField]
I have tried the following code I found on line. It doesn't throw an error . . . but it doesn't perform the Insert either.
Code:
Dim ws As DAO.Workspace 'Current workspace (for transaction).
Dim db As DAO.Database 'Inside the transaction.
Dim bInTrans As Boolean 'Flag that transaction is active.
Dim strSql As String 'Action query statements.
Dim strMsg As String 'MsgBox message.
[code]....
View 1 Replies
View Related
Dec 24, 2012
I have an Access db in a 3 person multi-user environment on a Windows network.
There is a "PRODUCTION" db and a "DEVELOPMENT" db.
Let's call them PROD and DEV.
PROD has the most current data, shipping records, item master, customer data, sales, etc. - but not the most current structure.
DEV has the most current struture - all the front end stuff - forms, functions, modules, etc. - but not the most current data.
The way I've handled this in the past (it seemed to work) was to take the PROD db and rename it to PRODX. Then take the DEV db and rename it PROD. Then open DEV (now called PROD) using the usual shortcut to PROD.
So now that opens fine, and I have to update all the tables from PRODX - and I mean ALL of them - since I don't really know what data has changed since the last update.If I try to delete records I get blocked by access because of all the related records (I don't have cascade delete set on every relationship). So I delete the whole table - ALL of them (this requires me to also delete the relationships). Then I IMPORT all the tables from PRODX (these have the current data). But now the relationships are all gone. It seemed at first that the relationships were back and intact - but when I last looked they were gone. So this is my problem.
HOW do I COMPLETELY empty ALL of the tables - or even delete them - and then restore them or repopulate them from an exact copy of the db but with current table data - and WITHOUT affecting the relationships?
Obviously any back end structural changes have to be handled differently. Usually by manually making the same change on the PROD db that I had in the DEV db - because deleting the table will cause the structural changes to be lost.
One last thing - I've been working this way on an un-split db, and now I'm in the process of splitting it - which SHOULD make updates much easier.
View 5 Replies
View Related
Oct 26, 2013
i'm running a module from an access db. how do i import a table from oracle into a closed access db, not the one where the code is running from?
View 1 Replies
View Related
Oct 23, 2006
I'm trying to import about 18 excel spreadsheets into one database in Access. I've been using "get external data" to import the spreadsheets as tables, and the first 8 of them worked fine, but now I cannot import any more excel files. The only error message I'm getting is "An error occurred trying to import file 'C:....xls' The file was not imported." And this error pops up after I have gone through all of the importing steps. Did I exhaust Access's resources? It's not a format issue, and I've restarted, etc. I'm at a loss.
View 2 Replies
View Related
Oct 7, 2013
Running Office 365 (Access 2013). Recent problem occurrence. When I do an 'External Data' command, either from another Access database or an Excel file, the import completes, but I don't see the new table immediately in the navigator section. If I import from another Access database, the new imported tables appear as '~TMPCLP....' tables. If it's from an Excel workbook, the new table doesn't appear at all. It's only after I exit the database and reopen it do I see the new tables correctly named.
View 1 Replies
View Related
Nov 6, 2013
I'm trying to find all the table names inside an external access file from a path.
I have a code for the user to select a file which return a patch to the file.
Then I need to find all the tables names from that file and append them to a list.
so I them can input them into a code and link the tables.
here is the code to get the file patch:
Code:
Function getFileName(path) As String
Dim f As Object
Dim varFile As Variant
Dim path
Set f = Application.FileDialog(3)
[Code] ......
View 8 Replies
View Related
Apr 7, 2014
VBA code to filter the table :
Code:
Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim flnm As String
Dim appXl As Excel.Application
Dim bookXl As Excel.Workbook
Const wrksheetName As String = "Welder Performance Overall"
[Code] ....
if i use DoCmd.OutputTo function, its export the whole table to excel. how can i filter this table ?
Environment : Ms.Access 2010
View 12 Replies
View Related
May 13, 2014
I have a PIVOT table that is used to select courses. I have some vba code that exports this to pdf, but it exports the entire data not the filtered data. Is this possible just to export the filtered data?
View 8 Replies
View Related
Jan 17, 2015
I want to hide external data and databasetools tabs only from my access program. How i do it ?
View 3 Replies
View Related
Apr 2, 2014
I have a function that exports a number of tables within an access 2007 database to one spreadsheet using docmd.transferspreadsheet.
The error message in the title of this post presents itself during a loop which transfers the tables to the spreadsheet. However, it doesn't always happen, sometimes it completes the process perfectly which is quite frustrating.
Below is the section of code where the error occurs ....
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_Table_Exports WHERE Type = 'CRM'", dbOpenSnapshot)
rs.MoveFirst
Do Until rs.EOF
TableName = rs.Fields("Table")
WSName = rs.Fields("WSName")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, TableName, NewWBPath, True, WSName
rs.MoveNext
Loop
View 1 Replies
View Related
Sep 15, 2014
I'm currently using the following VBA to export all tables within my DB to Excel on separate tabs:
Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String
out_file = CurrentProject.Path & "" & "Backup.xls"
Set db = CurrentDb()
For Each td In db.TableDefs
If Left(td.Name, 4) = "MSys" Then
'We do not need MSys tables in excel file
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
td.Name, out_file, True, Replace(td.Name, "dbo_", "") 'We do not need dbo prefix in sheetnames
End If
Next
But upon exporting I get the following error:
"Run-time error 3274' External table is not in expected format"
It then hightlights the following line:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
td.Name, out_file, True, Replace(td.Name, "dbo_", "")
If i navigate to the directory i can see that it has exported it to excel(attempted), but half of the tables(tabs) are missing.
View 1 Replies
View Related
Aug 1, 2013
[URL]
I have a button on a form, when it is clicked it opens a video file located in a folder on the local computer. Each time the button is clicked a security pop up appears that says "Opening file://C:lahblahyadayadaFile.mp4 Some files can contain viruses or otherwise be harmful to your computer. It is important to be certain that this file is from a trustworthy source. Would you like to open this file?" options are open or cancel. How can these messages be turned off?
I have already played around in the trust center of Access 2010 and changed the "trusted documents", "trusted locations", and "message Bar" tabs with no results. Do I have to play with regedit in windows to get rid of these pop ups? Or can I somehow use VBA code to disable them?They are just a nescience.
View 2 Replies
View Related
Jun 5, 2015
I have Access 2013, Win 7, Outlook 2010 (32-bit).I want to send an email from Access, but not through Outlook. Instead I want to connect to an external SMTP server. I've found a few examples that doesn't work for me. With code including for example
Code:
Set ObjMessage = CreateObject("CDO.Message")
I get an "Error 429: Object can't be created in Active X-component" (My translation from Swedish).With code including this:
Code:
Dim oEMail As New CDONTS.EMail
I get "user defined type has not been defined" (my translation again).I have working code for sending email through Outlook, which works fine. But, Outlook adds a lot of span tags, making it impossible to read for certain screen readers used by visually impaired people, therefore I can't use Outlook.
I've tried to read up on the matter (CDO), and think that maybe the CDO library etc is not included on my computer?can I download it?
View 10 Replies
View Related
Sep 27, 2005
Hi,
I would like to link to an external data source (table) from MS Access 2003. I attempt to use the 'Import' feature to do this, and select 'ODBC Connection'. I am trying to connect to an application called 'Action Remedy'. I make the connection fine, but when Access attempts to import the table, it does not like one of the Action Remedy field names, and the Import aborts. The name is something like "Call Status-History.Closed.Time". Obviously, Access does not like this field name. And the chances of getting it changed (on the A/R backend are slim to none). Thus, my question: Is there away around this ?
Thanks !
View 1 Replies
View Related
Jan 17, 2015
i want to hide external data and databasetools tabs from my access program.
how i do it ?
View 3 Replies
View Related
Nov 24, 2014
I have an Access 2013 database which will generate a pdf report and attach it to an email using the code:
Code:
DoCmd.SendObject acSendReport, "Report Name", acFormatPDF, Nz(To email address, a), , , "Email Subject", "Email body", True
I would also like to automatically attach on the server to this email. Such a file stored such as servernamesharefilename.pdf. Is it possible to do this?
View 14 Replies
View Related
Mar 5, 2008
Hi,
I'm new to this. I'm trying to enter data (it's actually Latitude and Longitude co-ordinates) from an existing Excel source into an Access database which has input masks of 00°00'00.00"L;0;0 (Latitude) and 000°00'00.00"L;0;0 (Longitude) in the respective fields. However I cannot get the information to import or display correctly. I did an "export data" of the respective table (hence fields) to Excel to try and get the correct entry format. An example of the Lat exported was 24°49'41.81"N and Long was 067°01'44.02"E (but with a very small ' in front but only visible in the data entry line in Excel, not in the actual spreadsheet table???)
However when I try to enter the data (even using the exact same little degree symbol, apostrophe, and quotation marks) it does not enter the access fields correctly. On closer scrutiny of the exported Excel format I note a small ' at the very beginning of the 24°49'41.81"N or 067°01'44.02"E string. But as I said previously only visible in the data entry line next to the formula button. Not on the spreadsheet cell.
However even when I "Paste Special" "values only" my new co-ordinates into the same entry location as one exported, it will still not import, or display correctly. If I go into the Access database directly there is a form where if I need to enter the new co-ordinates (using lat example above) I only have to enter 24 49 41 81 N (spaces between) and it will show correctly as 24°49'41.81"N
I'm getting desparate as I don't want to have to change all the details manually. Anyone know what my correct format from an Excel spreadsheet should be?
Apologies for lengthy story! Difficult to describe problem with degree symbols etc
View 2 Replies
View Related
Jan 4, 2013
create a datasheet from 2 odbc linked tables which I will be exporting to Excel. The resulting datasheet will have four columns, 'Purchase Order Number' 'Stock Code' 'Delivery Date' 'Unit Price'. The information I require in the datasheet is a unique stock code (one occurrence of each stock code) with the most recent unit price based on the last delivery date for each product. The linked tables contain many Purchase Orders for the same stock codes over a time period of a number of years where the unit price has varied.
View 3 Replies
View Related
