ms-access,access-vba , Iterate Databases and Delete Query
Iterate Databases and Delete Query
I am trying to iterate a table with database names in it, open the database, check if a query exists, if it does delete it, then import a query from the database this code is being executed from. I keep getting a compile time error on the line of
Set badqueryname = "qry_InformationMailer" and what I am trying to do with that is set the name of the query to delete. This is the error that line throws
Option Compare Database
Option Base 1
Private Sub fur()
Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rstTableName As DAO.Recordset
Dim myArray() As String
Dim intArraySize As Integer
Dim iCounter As Integer
Dim qryLoop As QueryDef
Dim exists As String
Dim dbs As Database
Dim badqueryname As String
'Compile error of object required on below line
Set badqueryname = "qry_InformationMailer"
Set rstTableName = CurrentDb.OpenRecordset("tbl_Data")
If Not rstTableName.EOF Then
intArraySize = rstTableName.RecordCount
iCounter = 1
Do Until rstTableName.EOF
myArray(iCounter) = rstTableName.Fields("ProgramName")
iCounter = iCounter + 1
If IsObject(rstTableName) Then Set rstTableName = Nothing
Set qd = CurrentDb.QueryDefs("qry_InformationMailer")
Set ws = DBEngine(0)
For l = LBound(myArray) To UBound(myArray)
Set db = ws.OpenDatabase("C:\" & Trim(myArray(l)) & ".mdb")
For Each qryLoop In CurrentDb.QueryDefs
If qry.LoopName = badqueryname Then
exists = "Yes"
DoCmd.DeleteObject acQuery, badqueryname
On Error Resume Next
db.CreateQueryDef qd.Name, qd.SQl
Set db = Nothing
Set when you assign a value to a simple variable type.
'Set badqueryname = "qry_InformationMailer"
badqueryname = "qry_InformationMailer"
Or, if you wish, you could use
Let instead of
Let badqueryname = "qry_InformationMailer"
But I don't see
Let used very often anymore.
Actually, in your code the value of badqueryname never changes again after you assign its value. In other words, it's being used like a constant, so you could just make it a constant.
Const badqueryname As String = "qry_InformationMailer"
One of those changes should eliminate that object required error. However, then you will encounter an error with the undeclared qry in this line ...
If qry.LoopName = badqueryname Then
Option Explicit to your module's Declarations section and then run Debug->Compile from the VB Editor's main menu.
The compiler should also complain here because l is undeclared ...
For l = LBound(myArray) To UBound(myArray)
Fix anything the compiler complains about and then run Debug->Compile again. Repeat until no more compile errors.
In my current Database I have a table whose data is manually entered or comes in an excel sheet every week. Before we had the "manual entry option", the table would be dropped and replaced by the excel version. Now because there is data that only exists in the original...
Here is my SQL query which returns number of days by subtracting current date from specified date and returns exact as i need, but in addition i want to add 1 to result if current time passes 14:30 or 2:30. My query SELECT reservations.customerid, DateDiff("d",reservations.checkin_date,Now()) AS Due_nights FROM reservations Am...
There is a standard company form currently in Word format (could use a different technology) with fields. How do I extract the data into my Access DB from this fields?. I am also open to advice of which technology would suit my requirements better with the requirements being: Need document...
Here is the data I am working with in MS Access from a system tracking when a agent makes system changes: |agentid|eventtype|reasoncode|eventdatetimelocal | |1830 |2 |32762 |01/01/2014 7:11:44 PM| |1830 |3 |0 |01/01/2014 7:13:46 PM| |1830 |2 |32762 |01/01/2014 7:14:55 PM| |1833 |2 |0 |01/01/2014 7:11:35 PM| |1833 |3 |32762...
I have a search button that executes this applyfilter macro code: [ContractNumber] Like "*" & [Forms]![frmContractMatch&Revenue]![Text44] & "*" So it searches text box text44 for the appropriate ContractNumber. The trouble I am having is that if a contract number is ART#45 for example, it will not find it in the...
I am looking to calculate a percentile of a subgroup for a field that can be NULL. Field IU is either 1 or Null. Specifically: *my table: tblFirst250 *group by: IU = 1 (which is Nullable) *percentile of: GM (which is Nullable) I am starting with the following (but I...
I am adding a browse button on an Access 2010 form; it will only allow me to attach files, not folders. Here is the code: Option Compare Database Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _ "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long Private Type OPENFILENAME lStructSize As Long hwndOwner As...
I need to execute four queries and then if there is success must return true otherwise false. The queries affect the database but the function returns false Private Function save_to_data() Dim success As Boolean = False Dim conn As OleDbConnection = GetDbConnection() Dim total_due As Decimal = sanitize(txt_total_due.Text) Dim amount_paid...
My update query update tbl_Clients set [email protected], [email protected] where [email protected] When I am updating this code in giving error Syntax error in update statement...
I have a table on which I run a query that I export to Excel. Here is an example: Table: Food Item | Price | Limit_Date | ------------------------------- Carrot | 0.80 | 08/07/2015 | Salmon | 4.30 | 01/07/2015 | Biscuits | 2.40 | 15/12/2015 | Milk | 1.00 |...
Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...
I've read all the faq pages from the python odbc library as well as other examples and managed to connect to the DSN, using the following code: cnxn = pyodbc.connect("DSN=DSNNAME") cursor = cnxn.cursor() cursor.tables() rows = cursor.fetchall() for row in rows: print row.table_name but for everything else I keep getting...
I have a table created to hold all my client data. The data is all entered in via form. When a new record is created (new client added) I also need to create an opening transaction balance on a separate table. So what is the best way to... 1) Identify...
I tried to translate a code from VBA excel to access. My data is a column of prices and I want to compute the returns. This is the original VBA code in excel: DerCol = Cells(T.Row, Columns.Count).End(xlToLeft).Column Cells(T.Row, DerCol + 1) = "Returns" For i = T.Row + 2 To...
I am trying select * from table where Contact Is Not null but it is displaying values including empty values...
I recently came across vba update statements and I have been using Recordset.Edit and Recordset.Update to not only edit my existing data but to update it. I want to know the difference between the two: recordset.update and Update sql Vba statement. I think they all do the same but I...
I am trying to add n columns to a table, like in this example of code where n = 10: Sub toto() Dim db As Database, i As Integer Set db = CurrentDb() For i = 1 To i = 10 db.Execute " ALTER TABLE time_series " _ & "ADD...
I want to run a for-loop from sometime to some specific time. Let's say from the first day of the year to the last day: I am given the year and I need to add the month and the day to it: I am trying to concatenate into a full...
I am working on a project in ACCESS 2010 that requires me to give a rank to 30000 products based on their sales. I have tried using a query to do the ranking and it takes a long time. (Please find codes at Making the ranking query efficient) I figured...
Is it possible to open a second Access database from within an Access database, and edit a query in that second DB? I know you can open one Access DB from another, but I'm just not sure whether or not you can edit a query that way. If it's possible,...
I would like to launch a MS Access macro from the command line (it's being run from another tool) to generate an output. Thing is, I'd prefer it if it could supress access from loading it's interface. I just want it to run the macro and shut down. Any thoughts...
I am having trouble trying to create a table using MS Access. I know for sure my problem lies in the "datetime" field but I can not figure what I am missing or doing wrong. When I click "run" I get the "Syntax Error in Field Definition" Create Table Appointments...
I have a list named Employee Dates, this list contains the columns: Employee | CPR Completed | CPR Required | ETC These columns keep going on for all of the training courses required for our employees with alternating columns for completed and required dates. I am using a workflow to...
I have a From and To DateTimePicker. I converted them into a String and use it to filter a MsAccess database of a specific range but there is a problem with the filtering. For example, I would like to filter from January 1, 2015 to January 31, 2015. The results...
In production we have 3 shifts. Each Shift timing is described in table tbl_ShiftSched: WT - work time, PT - break time. ShiftTmID - schedule for 2 and for 3 shifts. I am looking for easy way to get work time in minutes having start and end time. For example,...
I have a table with a time in a datetime field on an MSSQL2k8R2 server. I have linked the table in Access 2007. The table was migrated with the migration assistant from Microsoft. i.e. the Managemantstudio will give on SELECT TOP 3 [AbZeit], [AbBrP] FROM [dbo].[tSollFahrten] the Result AbZeit AbBrP...
I am receiving a "Subscript out of range" error when calling the LBound() and UBound() functions on a global object's array in MS Access 2003 VBA. I don't understand why this is occurring because I am certain the array has been initialized since I can access values from the array...
Ok, so I've been looking around here for a few days (and a few other sites) and while I have modified my code a good bit to find a solution, it still doesn't work. I have four source queries, and I'd like to set the record source of my report...
Given a database object in MS Access VBA, how can one get that database's VBProject? Function GetVBProject(ByVal db As Database) As VBProject Set GetVBProject = ??? End Function The only way I know how to get VBProjects in Access is through Application.VBE.VBProjects.Item(???). However, I won't know what order of the...
I am building a function with a set of supporting sub-functions to create ADOX.Catalog objects to help me build automation for Access database generation. I like to use late-binding for my applications because my user base doesn't always have the same version of office applications, so I can't always rely...
I have this ms access query: SELECT t1.sb, suchbegriff2, menge FROM (SELECT artnr & '/' & [lfdnr-kal] AS sb, left(suchbegriff,7) & val(right(suchbegriff,4)) AS suchbegriff2 FROM kvks WHERE suchbegriff like '*/*') AS t1 INNER JOIN (SELECT artnr & '/' & [lfdnr-kal] AS sb, [artnr-hz] & '/' & val(lfdnr) AS hz, menge...
This code and another is producing the logical error in Ms-Access where it asks for a parameter equal to an objects name. In this case it wants [DSRT_ERS].[ID]. INSERT INTO DSRT_ERS SELECT * FROM DSRT_TEMP WHERE [DSRT_ERS].[ID] <>[DSRT_TEMP].[ID]; If you look at the DB's documentation you notice it is spelled...
I have an Access 2010 odbc front end with a SQL Server 2012 back end. My images (.bmp) are stored as Varbinary(Max). When I insert an image using my bound object frame in an Access form, the image displays on the form and report perfectly. I had a large number...
I have reservations table to store hotel reservations, this table has reservation_id, customer_id, rommno, and checking date also there is another optional field, checkout field which will use when person checked out and also the status field will updated to checkout status wile firstly putted checking status. i need a...
I have a form where a user will select a value from a dropdown. Based on this selection, I would like a series of queries to execute. For example, if a user selects 'A', then queries 1, 2 and 4 would execute. If user selects 'B', then queries 4, 2,...
Hey all I'm trying to parse out any duplicates in an access database. I want the database to be usable for the access illiterate and therefore I am trying to set up queries that can be run without any understanding of the program. My database is setup where there are...
I am having a problem with my code where i am only able to add so many lines of text before i get an error "system resources exceeded". This is my code: Dim x As Integer = MsgBox("Update Record?", MsgBoxStyle.YesNo, "Are you sure?") If x = MsgBoxResult.Yes Then Dim accessconn...
I am trying to iterate a table with database names in it, open the database, check if a query exists, if it does delete it, then import a query from the database this code is being executed from. I keep getting a compile time error on the line of Set...
This gets the 25th percentile value of the GM field when field GICS Sector = "Energy": SELECT tblFirst250.[GICS Sector] , 0.75*( SELECT Max(GM) FROM tblFirst250 WHERE tblFirst250.GM IN ( SELECT TOP 25 PERCENT GM FROM tblFirst250 WHERE tblFirst250.[GICS Sector ]= "Energy" AND GM Is Not Null ORDER BY GM)) +...