oneidía

You should be using integrated security. But since we don't control all the things, sometimes database password authentication is forced on us. You can set up a file DSN with the password, but in MS Access it is never saved.  Instead everytime you reopen the database and view a table you see this:   Here is the workaround. Create a special type of query called a Pass-Through Query which does store passwords Create a macro that runs this query at startup This works by initiating a special silent query at startup using the password. Once the database password has been used once, it is cached for the MS Access session. Create the Pass-Through Query On the Ribbon choose the Create Tab and click Query Design Close the Show Table dialog Click the Pass-Through button on the Design Tab Click Property Sheet to edit the connection string properties. You can paste a connection string or use the ... builder to open a GUI. If you use the builder be sure to choose to Save t...

Feeds works brilliantly, but there is a couple weird gotchas when working from MS Access to Drupal that are worth documenting: Seems self-explanatory, but since you are importing as .txt or .csv make sure you remove all carriage returns .  In MS Access this involves writing an update query and doing a replace on all Chr(10) and Chr(13).  e.g. Replace(Replace([myfield],Chr(10),"<br />"),Chr(13),"<br />")   Although CCK Date fields import perfectly as MS Access dates, published dates do not.  This is because the published field in Drupal is a Unix Timestamp which is a quite different than Access dates.  The easiest way I know to fix this is to export your data to Excel and then use the Excel integer date value to convert to a Unix Timestamp. We've all seen this right?  That really long integer you see when you misformat a date?  Anyway, to fix you need to first account for the difference between the start dates of the two time systems and ...

Recently I was surprised to find that exports of Memo fields to csv and txt truncate at 512 characters. Thankfully, there is an easy, albeit poorly documented hack.  In the export wizard set "Text Qualifier" to "{none}"

When attempting to run Microsoft Jet Connection to MS Access Databases on a 64bit Machine you will get the following unhelpful error: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. Googling will point you on a couple crazy paths involving recompiling your application in 32 bit mode. The solution isn't that complicated if you are using IIS7. In IIS7 right-click on your application pool and choose "Advanced Settings" In the dialog that opens, set "Enable 32-bit Applications" to "True". There I fixed it.

After lazily just pasting code in <pre> blocks for years, this morning I finally got around to researching a code highlighter like you see on almost all the programming websites. Thanks to SyntaxHighlighter adding this feature was embarrassingly easy. It is self-contained javascript that works on a pre or script tag decorated with a html class calling a brush alias, e.g. <pre class="brush: sql"> INSERT INTO MyWebsite (pre, script) VALUES (complete, awesomeness) </pre> Oh, but it's not just SQL, no there's a whole easel of brushes included: Brush name Brush aliases File name ActionScript3 as3, actionscript3 shBrushAS3.js Bash/shell bash, shell shBrushBash.js C# c-sharp, csharp shBrushCSharp.js C++ cpp, c shBrushCpp.js CSS css shBrushCss.js Delphi delphi, pas, pascal shBrushDelphi.js Diff diff, patch shBrushDiff.js Groovy groovy shBrushGroovy.js JavaScript ...

Had one of those, "am I on crazy pills?!" problems the past few weeks. In an MS Access database I had a whole slew of SQL Server Linked Tables that included User-Defined Data Types(UDT). The UDTs were mostly auditing fields that referenced defaults to pull in the system.user and current datetime. Everything worked great. That was until I added a field and asked a user (with very elevated privileges) to refresh the linked tables. They did, but the audit fields referencing the user-defined types disappeared. At first, I thought it was some silliness in their file-based DSN, but even using my connection (with Windows-based security... so it pointed to their account) the audit fields remained hidden. To make the whole story more perverse, if I used the same connection logged in as me (I am a dbo) the tables refreshed with the audit fields and they could also see them. After much googling I finally stumbled upon this post and this msdn article which indicate that us...

In SQL Server views I've been using CASE statements to look for nulls for years. I've just nullified this practice. For the following Employees Table: EmployeeID LastName FirstName Nickname 39 Carter James Jimmy 40 Reagan Ronald 41 Bush George 42 Clinton William Bill Would be used to create the View: EmployeeID LastName Handle 39 Carter Jimmy 40 Reagan Ronald 41 Bush George 42 Clinton Bill This is the CASE: SELECT EmployeeID , LastName , CASE Nickname WHEN Null THEN FirstName ELSE Nickname END As Handle FROM Employees ISNULLified by: SELECT EmployeeID , LastName , ISNULL(Nickname, FirstName) AS Handle FROM Employees

This post is part of an ongoing series Design for Exceptions Spreading the Sheets and Letting Relations In Forming a New Reality There's more to come... we're starting simple and working up... this will slowly become more complex to include SQL Server, ASP.Net web apps and ArcGIS. Download the MS Access 2007 Project used in this post. Now that we have our relationships all set up creating usable edit forms in MS Access 2007 is ludicrously easy. Creating an Edit Form in MS Access in Four Satisfying Steps: Open Beer, Open Access Project Highlight the table for which you want to create a form (let's start with Cities) Click the Create Tab, Select Form Press Save, Sip Beer I'm not kidding. It is that easy. Here's my form (sips beer) Repeat four steps for ZipCodes: Wow! We are amazing. What else is there to do? (sips beer) And that's the problem here, we're just sitting around sipping beer. This isn't real data. Well, it is real data, but it's a t...

This post is part of an ongoing series Design for Exceptions Spreading the Sheets and Letting Relations In Forming a New Reality There's more to come... we're starting simple and working up... this will slowly become more complex to include SQL Server, ASP.Net web apps and ArcGIS. In my last post I went off on relational database design patterns. Now let's try implementing these patterns in MS Access. Download the MS Access 2007 Project used in this post. In the old days we made spreadsheets. And they got ugly really quick. Let's work with our city-zipcode analogy from last time . We started with what is basically a spreadsheet in MS Access: We outgrew this quickly when we added Meridian: I didn't mention this in my last post, but I see this all too often. Basic database design cue, if you're numbering your columns your design is wrong. Why? Boise has 29 zipcodes. New York has 161. Washington, DC has 278. Do you really want 278 columns for storing zipc...

This post is part of an ongoing series Design for Exceptions Spreading the Sheets and Letting Relations In Forming a New Reality There's more to come... we're starting simple and working up... this will slowly become more complex to include SQL Server, ASP.Net web apps and ArcGIS. One of the biggest mistakes I run into every day is databases and spreadsheets which weren't designed for the exceptions. I get it, most of your data fits in your model. But then you run into data that doesn't fit your world view and you call me. And that's when things get ugly. We need an example. Let's use ZipCodes. We all know how these work right? Each city has a zip code. Here's some examples from here in Southwest Idaho: City ZipCode Eagle 83616 Kuna 83634 Idaho City 83631 That wasn't so hard was it? Our spreadsheet is done! Not so fast cowboy. What do we do with Meridian? It has three Zips: 83642, 83646, 83680. You've seen this before though and you got it t...

You know those annoying Microsoft Office Macro Security Warnings? There is a way to make them disappear, at least for you. Here's the Steps. Find the Microsoft Office Tools folder generally under Start > All Programs > Microsoft Office > Microsoft Office Tools Select Digital Certificate for VBA Projects (if you don't have such a link see this article ) Fill out the form and name it "Whatever makes you happy" Open your Microsoft Office Application and Open Visual Basic Editor Navigate to the Macro that is causing you Warnings Go to Tools > Digital Signature and click Choose Highlight the Certificate you just created, e.g. "Whatever makes you happy" Restart the Microsoft Office Application This time when you get the security warning choose, "Always Trust this Publisher" No more messages!

For the best result in an MS Access query split the string into segments and use the DateSerial function: GMTDate: IIf([GMT_Date]="",Null,DateSerial(Mid([GMT_Date],7,4),Mid([GMT_Date],4,2),Mid([GMT_Date],1,2))) where dates are written in the format dd/mm/yyyy (23/12/2008). When combining date and time fields from string use DateSerial + TimeSerial, e.g. GMTDateTime: DateSerial(2008, 9, 23) + TimeSerial(10, 39, 1))

How do you search for * and ? (asterisks and question marks) in MS Access? Bracket the criteria: SELECT * FROM Table WHERE Column NOT LIKE "*[*]*"

Ever get this error on a create table or insert query? The work around is quite simple. Cast the autonumber as an integer. In query design view change your field definition for one of your autonumbers to read: XID: CInt([ID]) Or in SQL View: CInt([ID]) AS XID (Ran into this error message again this morning and it reminded me I should share the workaround.)