How to Round in MS Access, VBA
Whats the best way to round in VBA Access? My current method utilizes the Excel method Excel.WorksheetFunction.Round(... But I am looking for a means that does not rely on Excel. ...
ms-access
SQL Null set to Zero for adding
I have a SQL query (MS Access) and I need to add two columns, either of which may be null. For instance: SELECT Column1, Column2, Column3+Column4 AS [Added Values] FROM Table where Column3 or Column4 may be null. In this case, I want null to be considered zero (so 4 + null = 4, null + null = 0). Any suggestions as to how to accomplis...
Determining whether an object is a member of a collection in VBA
How do I determine whether an object is a member of a collection in VBA? Specifically, I need to find out whether a table definition is a member of the TableDefs collection. ...
Easiest way to decrypt PGP-encrypted files from VBA (MS Access)
I need to write code that picks up PGP-encrypted files from an FTP location and processes them. The files will be encrypted with my public key (not that I have one yet). Obviously, I need a PGP library that I can use from within Microsoft Access. Can you recommend one that is easy to use? I'm looking for something that doesn't require ...
Combining split date ranges in a SQL query
I'm working on a query that needs to have some data rows combined based on date ranges. These rows are duplicated in all the data values, except the date ranges are split. For example the table data may look like StudentID StartDate EndDate Field1 Field2 1 9/3/2007 10/20/2007 3 True 1 10/21/2007 6/12/2008 3 True 2 10/10...
How can I programmatically repair (not merely compact) an Access .mdb file?
I have a corrupt database. If I open it in MS Access, MS Access offers to repair it, and it succeeds. How can I do that with code? On a machine where MS Access is not installed. I know from trying it that JRO.JetEngine.CompactDatabase does NOT work. In other words, I want to do what Access or JETCOMP.exe is doing, not what JRO.JetE...
How can I modify a saved Microsoft Access 2007 Import Specification
Does anyone know how to modify an existing import specification in Microsoft Access 2007? In older versions there used to be an Advanced button presented during the import wizard that allowed you to select and edit an existing specification. I no longer see this feature but hope that it still exists and has just been moved somewhere el...
Classic ASP SQL Injection Protection
What is a strong way to protect against sql injection for a classic asp app? FYI I am using it with an access DB. (I didnt write the app) ...
MS Access MDE on Terminal Server
I deployed an MDE file on the terminal server and when I double clicked the MDE i have the following error. " There isn't enough memory to perform this operation. Close unneeded programs and try again. I never had any issues on local machine. I tried de-compiling and compacting and again compiling. I can open other forms but only Swit...
How to stop the Access 2007 Configuration Progress when switching versions
Like many developers I need to run more than 1 version of MS Access. I have just installed Access 2007. If I open Access 2003 and then open Access 2007 I have to wait 3mins for the 'Configuring Microsoft Office Enterprise 2007..." dialog. Then if I open Access 2003 again it takes another 30secs or so to configure that. PLEASE NOTE:...
How do I make foreign-key combo boxes user-friendly on an Access form?
I've got two tables: Employees: uid (number) | first_name (string) | last_name (string) | ... Projects: uid | project_title (string) | point_of_contact_id (FK: Employees.uid) | ... I'd like to create a form for Projects with a "Point of Contact" combo box (dropdown) field. The display values should be "first_name last_name" but the ...
Microsoft JET SQL Query Logging or "How do I debug my customer's program?"
The problem: We use a program written by our biggest customer to receive orders, book tranports and do other order-related stuff. We have no other chance but to use the program and the customer is very unsupportive when it comes to problems with their program. We just have to live with the program. Now this program is most of the time ...
[] brackets in sql statements
What do the brackets do in a sql statement? For example, in the statement: insert into table1 ([columnname1], columnname2) values (val1, val2) Also, what does it do if the table name is in brackets? ...
How to filter a report object when saving through FileDialog in MS Access
I am attempting to save an rtf file using FileDialog and would like to filter using a where clause. This is what I have: Set dlgSave = FileDialog(msoFileDialogSaveAs) With dlgSave .Title = "Provide the place to save this file" .ButtonName = "Save As..." .InitialFileName = Me.cmbPickAReportToPrint.Value & "-" & Format(Date, "mmddyy...
How do I create a (Type, ID) (aka 'polymorphic')- foreign key column in MS Access?
In Ruby-on-Rails, this is called a "polymorphic association." I have several Commentable things in my application, the tables for each are below: Post id | title | text | author (FK:Person.id) | ... Person id | name | ... Photo id | title | owner (FK:Person.id) | path | ... I'd like to add a Comments table as follows: Comments id ...
How to update an Access DB from the web?
I'm looking for a way to create an online form that will update an Access database that has just a few tables. Does anyone know of a simple solution for this? ...
Wouldn't MS Access(.mdb) file size reduce after deleting the content of database?
I was inserting data into a MS Access database using JDBC-ODBC driver. The blank mdb file was 2KB. After populating this database, the size grew to 155MB. Then I was deleting the data. But I found the size of mdb remains the same as 155MB. I don't get any errors. But is it normal this way? I would expect the file size reduces. If...
Is it safe to run Access 2003 and 2007 at the same time?
My question about the reconfiguration delay when switching between Access 2003 and 2007 the comment was made: Btw, you can't avoid the reconfiguration between Access 2007 and earlier versions. Access 2007 uses some of the same registry keys as earlier versions and they have to be rewritten when opening Access 2007. If this is so then i...
Parsing Text in MS Access
I have column that contains strings. The strings in that column look like this: FirstString/SecondString/ThirdString I need to parse this so I have two values: Value 1: FirstString/SecondString Value 2: ThirdString I could have actually longer strings but I always nee it seperated like [string1/string2/string3/...][stringN] What I n...
Field default value from query in MS Access
I have a field on a table in MS Access, tblMyTable.SomeID, and I want to set the default value as a user preference in tblUserPref.DefaultSomeID. It doesn't appear that I can set the default value to use a query in the table definition of tblMyTable. I have a form where records are entered into tblMyTable. I've tried to set the defaul...