Modules & VBA :: Loop Through Access Databases And Get Table Definitions
Mar 9, 2014
I have 192 databases that I need to redact certain information in certain columns. Generally, I'm looking for field names like name, first name, last name, address, address2, shipping address, mailing address, and phone numbers. Not sure how to get this information without going into every database and every local table inside the databases. Is there a way to get this information programmatically and just access the databases and table that I need to redact info in?
View Replies
ADVERTISEMENT
Jul 13, 2013
As I proceed with my development I continue to rename fields. The effect of those renames is unclear to me. They seem to effect some things and not others.
What rules do I need to know about renaming the fields in my database and the effect on the forms I'm working on.
View 14 Replies
View Related
May 24, 2007
Hi
I am looking for a book or poster or something that can show me all the access controls and what they do. My VB.NET version came with several posters. I have looked at many books but can not find one that enumerates and explains all of them. I would also like to get similar information for the Active X controls included with access.
Thanks
View 2 Replies
View Related
Apr 27, 2005
If a front-end database has links to many tables in a back-end database and the back-end is moved, is there an easy way to update all the table links in the front-end in one go, or do you have to set up all the links again one at a time?
Hoping there's a quick way...
Dave
edit: just realised the previous post asks exactly the same thing ( :o ), but that hasn't elicited a solution yet ( :( ).
View 5 Replies
View Related
Nov 28, 2005
Hello,
Im a newbie at developing access databases. I have just finished creating my first application. The problem i have is that i would now like to create an exact copy of my table definitions without all the junk data i have been entering while i have been creating the application. I would like to use this copy so i can link the finished database to it. Could someone please offer me some advice as how to best go about doing this ?
Best regards,
Andy.
View 4 Replies
View Related
Mar 10, 2015
I would like Access to open an Excel file count the worksheets and then give each tab the name of the column heading in cell A1 of each worksheet. I had no problem with the code in Excel but when I tried to add it to Access I am getting an error on the WS_Count = obj.ActiveWorkbook.Worksheets.Count line. The error message is "Object variable or With block variable not set".
Here is the code:
Code:
'This block of code will cycle through each tab and name them
Dim WS_Count As Integer
Dim I As Integer
[Code]....
View 4 Replies
View Related
May 13, 2015
I am trying to loop through a table and count the duplicate records only once. Please see the attached file.
1) In the test table, I want to loop through all pt_accts.
2) In the countable column, if an acct repeats, I essentially just want to put "1" in one of the records. For the non-repeating accts, putting in a 1 is sufficient. Basically, I just don't want to count the duplicate ones multiple times.
In the example, acct "aaa" repeats, but there is just one "1" in the countable column. "eee" repeats as well, but there is just one "1" and the rest is 0.
Is this possible via VBA?
View 14 Replies
View Related
Apr 24, 2015
I have several tables and the default layout is one table per page. In some cases, there are a couple of fields...a waste of paper. Can this be changed?
View 1 Replies
View Related
Sep 2, 2014
I've set a database which has a table in which there are 2 fields "Account" and "Total Accounts". I want to have the amount of total summation of accounts in "Total Accounts" field of each record, which is the result of summation of "Account" values in all previous records till the current one. In order to do this purpose, I copied the value of "Amount" field of each record into "Total Accounts" field of the same record, at first. Then, I tried to add the amount of "Total Accounts" field of every record with just the amount of "Total Accounts" of previous one to earn the actual total amount of that record. I found that I need a VBA loop to do this query for all records (except first record) and so I code it as below, but it has the Run-time error '424' : Object required and it seems that I am in a mistake in definition of strSQL variable:
Code:
Private Sub doDataSegm_Click()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("Table1", dbOpenTable)
[Code] .....
View 3 Replies
View Related
Jan 25, 2014
I am trying to export a table in access based on a unique field called Group_Name. Say my table has 100 records. 10 of those records belong to Group1, 10 belong to Group 2, and so on. What I want to do is export those groups individually to an excel file and have that file named somting like Group1_Premium Detail Report.
Here is the Code I have so far. I have a feeling I am close. When I run the code it does not seem to like strrsql2
Dim strsql As String
Dim strsql2 As String
Dim strfilename As String
Dim strpath As String
strpath = "C:UsersDesktopHome"
[Code] ....
View 6 Replies
View Related
Feb 26, 2014
I have a table with a field that contains IDs, e.g.
123
456
789
I would like to generate a single string from this table, seperated by commas, e.g. 123, 456, 789 and output to a field in an existing table. This will then be used in a SQL statement.I am new to VBA and don't reallly know where to start/
View 9 Replies
View Related
Jun 26, 2015
I have an MS Access 2010 db that has a main form called switchboard. This has 4 command buttons that open diffrent forms. Also on the main switchboard form i have an unbound textbox called TxtUserName that captures the users environ"username" when the switchboard form is opened.
I have a table called "tblAccessUsers" that i manually enter who i want to use my db. This table will have up to 50 names added to it. Their is only one field name in this table and it is "User Login".
When the user hits any of the commandbuttons on the main switchboard form i need some code that will look at the value in TxtUserName and loop through tblAccessUsers for an exact match. If it finds a match then it will carry out the open form command or if not prompt the user with a message box.
My knowledge of Access and especially VB is quite limited. I managed to create this using a DLookup but that only returns the first record in the table. The logic works but it will not look past the first record.
View 3 Replies
View Related
Oct 22, 2014
I have a VBA routine that runs some queries and then creates a report as a PDF document using my virtual PDF printer. At this point it brings up the Save As dialog and I give the file a name and select a folder to store it in.
But I run this report 30 times for 30 different parameters. The parameters are obtained from a table which contains values such as Cancer, Diabetes, Stroke
-I always name the PDF file according to this value.
-I always choose the same folder.
-I always overwrite the reports I produced last month (copies have been sent elsewhere by then).
how can I get VBA to cycle through this code 30 times, each time selecting the next value from my table and creating/overwriting those files without the need for me to tell it the filename and path?
Code:
Private Sub lstSpecialties_DblClick(Cancel As Integer)
At the moment I am running this code by double-clicking an entry in the Specialty listbox, 'but I would prefer the whole thing to run multiple times for as many Specialties as exist in the table '(currently 30 but could be more in time)
'Warnings off
DoCmd.SetWarnings False
'Make the tables needed for the cumulative queries
DoCmd.OpenQuery "Spec 002 Monthly recruits - part 2 - make table" Each of these queries
DoCmd.OpenQuery "Spec 005 Monthly recruits - part 2 - make table" uses the Specialty selected above
DoCmd.OpenQuery "Spec 022 ABF previous year - part 2 - make table" as a parameter to get
DoCmd.OpenQuery "Spec 025 ABF current year - part 2 - make table" the right data for the report
[code]....
View 5 Replies
View Related
Aug 16, 2013
Using Access 2010: I have a query with four fields: ORG_NAME, PERS_NAME_LAST, CountOfORG_NAME, and BdMbrCount. There are a couple hundred companies in the database with 1-7 people associated with each company. I need to number each person so that they have a number, 1-7 in the MemberCount field of my query.
I have my query connected to VBA code.
I have experimented with code that I have found on the forum, just to see if I could get something to work and I am getting “Undefined function ‘BdMbrCount’ in expression. I am trying to pass [ORG_NAME],[PERS_NAME_LAST] to my function and assign the value of BdMbrCount to a new field in my query, BdMbrCount.
Code:
Function BdMbrNumber()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourBdMbrsRRecognizedQry")
'Set rst = CurrentDb.OpenRecordset("SELECT [ORG_Name],[PERS_NAME_LAST],[CountofORG_NAME], [BdMbrCount] FROM YourBdMbrsRRecognizedQry")
Dim ORG_NAME As String
[code]....
View 14 Replies
View Related
Jul 14, 2015
I have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but how to write that as I have never used it before. Below is what I was using until this new request came up.
<code>
Private Sub txtloan1_AfterUpdate()
If IsNull(DLookup("[loan1]", _
"settlement", _
"[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then
Cancel = True
MsgBox "Test", vbOKOnly, "Warning"
End If
End Sub
</code>
This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.
View 7 Replies
View Related
Aug 23, 2013
I am trying to improve my code by making it more readible. The following code works, but it is certainly not the most efficient way. I`m trying to write a loop to make certain elements in an Access report visible/invisible, but I can`t address the visibility property of these items while iterating over i.
Code:
DoCmd.OpenReport "tblInterval subreport", acViewDesign, , , acHidden
Reports![tblInterval subreport]!BoxInsp1.Visible = False
Reports![tblInterval subreport]!BoxInsp2.Visible = False
Reports![tblInterval subreport]!BoxInsp3.Visible = False
[code]...
View 2 Replies
View Related
Nov 11, 2013
I have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.For this i tried to write a code but i think i m unable to do that .
Code:
Loop through files in folder
folderspec = "O:QA FilesQC ReportingPending Review"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.files
[code]...
View 7 Replies
View Related
Aug 16, 2007
I see that we can export a table, definitions only, from the master (developer) db into a client's (runtime) db.
But if there are relationships in the table, the export fails (Access 2003). How do we get around this problem?
And if the client's db is on another computer system, ie. remote from the developer, how do we import the new and amended definitions into the client's db?
View 5 Replies
View Related
Oct 28, 2013
I am using Access 2010...I am suddenly getting the error 3048: Cannot open any more databases...The error occurs on the line of code: Set db = CurrentDb()
Code:
Private Sub SetInUseFalse(TelesalesId As Long)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select InUseBy from tblTelesales where Telesalesid = " & TelesalesId)
Run Some Code
[code]....
Why should I suddenly get this error? The error appears to be in a loop. If I close the error message, it pops up again. I have to close the database using Windows Task Manager.Is there a maximum number of connections? But I religiously close connections as indicated above!.I am only connecting to the current database (backend form frontend)Am I opening / closing connections & recordsets correctly?
View 4 Replies
View Related
Aug 7, 2006
Hi Folks
I have a text box which shows the following
=DCount("[Ref number]","main","[open or clsoed] = 'open'")
This looks at a table with a primary Key called Ref Number in a table called main with a field called open or clsoed and looks for the value open only.
I need to specify another fileld, called engineer wheer I match the username =jimmy
Im struggling to add this extra field
So it wiill look , for Open and an engineer called Jimmy in a table called main that has a primary key set !
Can anyone give me some pointers on this simple question ?
Br
Jimmy
View 7 Replies
View Related
Mar 11, 2014
I have to loop thru 2 recordsets.I need to first determine the quarter , then retrieve a value from each of the 6 fields for that quarter and compare that value against a previous years value that is also stored in the table for each quarter.the following is the field names not the field value.
Tier 1 2 3 4 5 6 PrYr
Qtr1 Q1T1, Q1T2, Q1T3,Q1T4, Q1T5, Q1T6 .385
Qtr2 Q2T1, Q2T2, Q2T3.Q2T4, Q2T5, Q2T6 1.25
Qtr3 Q3T1, Q3T2, Q3T3,Q3T4, Q3T5, Q3T6 .774
Qtr4 Q4T1, Q4T2, Q4T3,Q4T4, Q4T5, Q4T6 .333
if Qtr1 = Q1T1 = 0.44 Q1T2 = .50, Q1T3 = 1.45, Q1T4 = 3.00, Q1T5 = .25, Q1T6 = 6.0
So I need to be able to set the value of PrYR = .385 and compare against the value of the 1st qtr for each tier for Qtr1.Then go to Qtr2 and repeat the process but grap the Q2 PRYR value = 1.25 and compare against all Tiers for Qtr2.As so forth for each quarter.then I need to compare the value of the PrYr and if it is the following then
If PctYrlyIncrease< Tier1 Pct (Q1T1) Payout = 0
elseIf PctYrlyIncrease> Tier1 Pct (Q1T1) and < Q1T2 then
Sum(TotalNetUSExp * T1E)
ElseIf PctYrlyIncrease> Tier2 Pct (Q1T2) and < Q1T3 then
Sum( TotalNetUSExp * T2E)
and repeat for each Tier per Each Qtr.Here is my code so far:
Code:
Public Function BkOvrCalc(ByVal gContractID As String) As Long
Dim curDB As DAO.Database
Dim strSQL As String, strSQL1 As String
[code]...
View 3 Replies
View Related
May 11, 2014
Here is the code:
Private Sub Form_Load()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("select * from tblpatient, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
' do stuff
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End If
End Sub
I have watched it step through on debug and it does everything right for the first record, but it seems to come back to the same record.
View 3 Replies
View Related
Feb 23, 2014
I have a form where a textbox has shows the NameNo from the NameNo column in a db. At the moment I am using a dlookup to set the textbox = to the NameNo, but obviously this is only returning the 1st value in the column. How to get this form to automatically display each NameNo, creating a pdf of the form and then moving on tto the next nameno. I have the code to make the pdf working so far, all I need now is to figure out how to use the textbox to loop through all the NameNo's in the table.
View 3 Replies
View Related
May 6, 2015
i have the following code to get file list from specified directory:
Code:
Dim db As DAO.Database
Dim TB1 As DAO.Recordset
Dim p As String, x As Variant
[Code].....
the field "File_Name " is not set to accept duplicate the above code work fine , but when it find the file name exist on the TB1 it return an error i need the code if find any error continue to retrive other files on the folder not exit loop and also i don't want to make check if file exist it will lower the speed of code
View 6 Replies
View Related
Jun 4, 2014
Is there a way of looping through a non update able continuous subform, and using information from each line.
Which creates a new record in another table? so if there are 3 records in the subform it creates 3 new records in the other table and so on?
View 3 Replies
View Related
Jun 30, 2015
why my access always go over the loop that I am declaring..Here is a sample of my code:
Code:
Dim rs As New ADODB.Recordset
rs.ActiveConnection = conn
rs.Open mySQL, conn, adOpenDynamic, adLockOptimistic
For lngProd = 1 To 5
[code]...
I get the error of access cannot find the tbl_data6. Where in the declaration I wrote 6??
View 1 Replies
View Related
