Can anyone help me with setting up a Visual Basic project to where the datasource I use is a Access database on my harddrive. This Access database isn't linked to a server or anything. It's just a database I created and would like for my project to open this file everytime it's attempting to pull data. How can I make this connection? If I am able to use ADO, how do I go about doing this? I would think ADO wouldn't be necessary though.
Would it be ok just to make a copy of the BE file (every so often) rather than to make a copy via code?The user can then just paste over the original if it becomes corrupt.
Hello, Is it possible for a [MSAccess] query to obtain table data from an [external] ODBC Datasource? Something like: INSERT INTO TableA SELECT * FROM ODBC;DSN=MyDSN;UID=;PWD=;.TableA
I found a similar thread but didn't understand the answer which mentioned linking a back-end to the Access Table.
I have a SQL table shown on a form which i wish to filter by current logged in user -
I have a local table storing the currently logged in user and all records in the SQL table have a field called userEmail
so - i have forms that take their datasource from a query which then only shows the user his/her records.
SELECT * FROM userDetails, dbo_TrackerMainData WHERE (((dbo_TrackerMainData.userEmail)=userDetails.user email));
that works fine - after much torment, i figured out that you must use the query as the datasource and not the table as the datasource with a filter, because that just didn't work.
anyhoo.
my problem then is that when i use a form in this way - ie, datasource is a query, i can't then modify any data in the table!
I need to have a few checkboxes on the form that set values - for instance archiveFlag and TestFlag are fields and i need to be able to change their value.
If i set the form datasource to the table (dbo_TrackerMainData) it works ok - my check boxes tick and set -1 (but i see ALL records)
if i set the form datasource to the query i see only 'my' records, but when i try to click the checkboxes nothing happens..
Not sure what section this should go in but it vaguley relates to access and VBA so it's in here.
After a complete nightmare trying to do some decent graphs in ASP i decided to try using excel linked to my Access DB.
I've set up my pivot tables in Excel to link to the Db on the webserver. The user can open the workbook from the website and view the reports and graphs.
The problem is the database contains sensitive data for more than one organisation.
I have a workbook for each organisation selected dynamically when the user logs in to the webpage.
I've set a parameters in the pivot table querys so that all querys will be filtered using the users organisation code.
Trouble is you can access the data source of the pivot table through the wizard and ammend the parameter to view other organisations data.
Is there a way of preventing the user from accessing this feature i.e locking the wizard?
I have an accdb file, holding all the tables on my server. Clients are linked to this one from their workstations. Speed is a constant issue mostly when more users are linked and I'm trying to find ways to make things faster.
Would it be possible to open a recordset when a form is loaded and set it as the form's data source? Would that make operation faster, at all?
The reason this idea came up is that it would be logical to use a small size recordset rather than large tables or queries that takes a while to load during starting form that use tables/queries with large number of records.
I have a table T_RollDate It only has one field, called RollDate It only has (and only ever has) one record (being a date)
I also have a form F_RollDate that has T_RollDate as record source, containing a text box that has RollDate as the control source.
It has been suggested to me that I can interrogate the value of the record in T_RollDate as if it were a constant, and use that constant in (eg) other select queries. To be precise, I was told that forms!F_RollDate!RollDate would return the value of that record.
I cannot get this to work (assuming that it should be possible). If I refer to forms!F_RollDate!RollDate in an query then when I run the query I am prompted to enter a parameter value for forms!F_RollDate!RollDate, where I had hoped that it would select the value of the only record in T_Rolldate.RollDate.
Hey guys.. I am trying to make my DB into an MDE file however that option which is in Tools > Database Utilities is not highlighted on this particular database. Anyone know why? Thanks!
EDIT - This features seems to be inactive on all atabases on my computer (I haven't tried another computer yet). I am using Access 2000 and running Win XP Hme. SP1.
I created an Access/VBA database with a number of forms, reports, tables etc and I want to make it an MDE file.
I split the database into a front end and back end, relinked the two and tested it fine. Then I read I had to convert to 2002-2003 format. Again I did that and relinked and retested, so far so good.
However, when I go into the front end and try to make an MDE file I have a problem. It asks me to select the database, so I point it at the front end (Dry Clean) and then it asks for the name of the MDE to create (Dry Clean1) after that if I look in the folder it immediately creates a db1 and then sits there eating up cycles until I cancel it.
I have let it run for over an hour and it is just a blank screen with 'Make MDE/ADE' down in the lower left. I look in task manager and it says it is not responding, but it is eating up most of the CPU and doing some IO (thus I let it run for an hour once). If I cancel it and go into db1 (which is not the name I specified) it says that the VBA files don't exist and just keeps popping up errors.
Is this a bug? Did I miss a step? Am I just doing this wrong?
I need to make this last step to 'blackbox' the code from the users to complete this project.
Any help would be greatly appreciated! :-) Thanks.
Hi at the moment I am using a bit of VBA code like below:
Public Const currentYear As String = "0708"
Static Function GetCurrentYear() GetCurrentYear = currentYear End Function
I then call this from my ms access sql statement with GetCurrentYear() am I correct in thinking this will only need to be evaluated once (I am just thinking in terms of performance) as it is a static function and a constant or is there a quicker way to do this. I couldn't see a way to easily get the value from a constant without a function. I may be missing something though thanks for any advice.
My DB has many (!) forms that are based on Crosstabs queries. The headers are years, and therefore my formfields has controlsources like 2014, 2015 and so on.
Example:
The table it comes from looks something like this:
The crosstab then takes the EndYear as a coloumn header. Then my forms use these fields, now named 2014, 2015, 2016 .... and give me a nice looking table showing the accumulated "end" for multiple projects.
The problem is when the year changes after 31/12, all fields must manually be changed. This means I have to manually change 2014 to 2015, 2015 to 2016 and so on in all of my formsfields controllsource.
I have played around to find a way to change the source of the field, and hoped I could say something like =year(now()) and for the next year say =year(now())+1 but this only returns that year as a value, and not as a controlsource...
The query functions nicely (ive changed the system time, and it has no issues with this structure), but the forms source is still in the previous year, and returns and error (of course).
When the year changes past new years eve, so will the headers (fieldname) change... but how to make the form change fieldsource?
I'm using A2003 to try and create an mde file but it is not playing ball. The mdb is compiling fine with no errors but when I click on "make mde file" and supply the path/filename, Access appears to try to open the mdb again and prompts me with a security warning saying that 'opening this type of file may be harmful.....etc'. The mde does not get created. Any ideas?
I have created a copy of an access file to make some changes to tables, forms and etc. Some of them are deleted, new ones created or the codes are changed.
Is there a way that I can apply all the changes to the original file?
I have a problem. This problem is that when I generate the report, I want to show a save file dialog box. How can I do this and and retrict the files show to only "*.pdf" files. I tried it as follow:
Private Sub SaveFile_Click()
Set FDialog = Application.FileDialog(msoFileDialogSaveAs)
With FDialog .Filters.Add "Acrobat Files", "*.pdf" .Show End With
End Sub
error is araised when executing .Filters.Add "Acrobat Files", "*.pdf": Object doesnt support property or method.
i have the following code to make the PDF form a report. but i want to make the PDF in landscape mode and also each time with a new name.
If Me.Combo3.Value = "" Or IsNull(Me.Combo3.Value) Then MsgBox "Please enter order number!", vbExclamation, "Order number required" ' ElseIf Me.cmb_AuftragNummer.Value Like "*[a-z]*" Or Len(Me.cmb_AuftragNummer.Value) <> 6 Then 'MsgBox "Invalid order number!", vbExclamation, "Invalid order number" Else DoCmd.OpenReport "rep_CQAReport", acViewPreview, , "[Fehlercode] = '" & [Combo3] & "'" DoCmd.Close acForm, "frm_rep_cqaReport_filter" DoCmd.OutputTo acOutputReport, "rep_CQAReport", acFormatPDF, "O:ApplicationsCQA ReportingPDFTest01.pdf", False End If
I tried to display a variable and constant onto a text box by putting =myvariable onto the data source of the text box. However, Access seems keep threating my variable as a data field and giving me error message. What to do with this?
I need to put together a make table query, already got all the fields sorted the way i want the problem is that query is located in my databases Front End and i need the table to be made in the Back End (giggity), the filepath for the back end is actually stored in a "Master Control" table if this makes the process easier...
I'm trying to split up the filename into four separate fields. Here's an example of the filename:
123112 427900 55261 1156833.jpg
A 12312 B 427900 C 55261 D 1156833
The values A, B and C are always the same number of characters. The last value, D, changes in size. It can be anywhere from four characters to twenty. But it always starts at position "20" as with "1" in the case of the 1156833. The extension, jpg, is of no value.I might also add my programming ability is close to nonexistent.
I'm trying to do the following with the code below:
1. If the file is not in your My Documents folder than goto errhandler and a message box pops up asking you to retry or cancel. 2. When the user clicks retry it goes back and trys again but if the file still isn't there a runtime error 1004 occurs.
I want it to continue to loop when the user presses retry or until cancel is pressed. I've been trying with the code below but no luck.
Code:
Public Function AddITARPicOffloadAnalysis() Dim xlApp As Object Dim wb As Object Dim ws As Object Dim Lastrow As Long Dim objFolders As Object Set xlApp = CreateObject("Excel.Application")
i want to make an excel file from a query and then export it to mail. i want to make an excel and then send it as an attachhment. also i want to insert the mail address to manually because each time it is sent to a different person.
I have a table that is populated everyday, with following columns:
1. ward (linked to the wards table) 2. date 3. number of patients
We have a total of 18 wards, wherein the daily number of patient in each ward should be recorded. The problem we face is that we find it counter-productive if the data encoder selects a specific ward (dropdown list), then puts the number of patients, and then moves to another field repeating the process. (the date is pre-selected using a combo-box and this will fill the date fields, thus the encoder selects the date only once).
I was wondering if there is a way where we can just automatically show all the wards, so that the data encoder would just proceed on putting the figures.
So a while back I created a database which I use to keep track of my companies large list of products. It is very simple only 3 tables, 1 query and 1 form. After running into a problem with copying and pasting updates on each computer in the office I decided to split the database on a network drive. This worked for a bit, however I had to add new fields and modify the form, which corrupted the file. Luckily I had a backup before the split.
As far as I could tell all I had to do was modify the backend file and make a new front end. However it seems like it is not as easy as I thought it would be. How do you modify a split database without corrupting the files or using a non-split copy?
