This is a bug typically met if grouping on a memo field. There may be several workarounds depending on your needs: Select a, Left(b, 255) As b From table1 Group By a, Left(b, 255) Select a, Mid(b, 1) As b From table1 Group By a, Mid(b, 1) Select a, First(b) As firstb From table1 Group … Read more
I also hate Access, but you must play with the cards you are dealt. Continuous forms are a wonderful thing in Access, until you run into any sort of complexity as is commonly the case, like in this instance. Here is what I would do when faced with this situation (and I have implemented similar … Read more
Dim x As Variant x = Null Only the Variant data type can hold the value Null. A Variant is a special data type that can contain any kind of data […] A Variant can also contain the special values Empty, Error, Nothing, and Null. The “point” of all the other data types is precisely … Read more
I am able to use this feature on my machine using MS Access 2007. On the Ribbon, select External Data Select the “Text File” option This displays the Get External Data Wizard Specify the location of the file you wish to import Click OK. This displays the “Import Text Wizard” On the bottom of this … Read more
You’ll need to declare this WinAPI function in order to call it from procedures in your code. From HERE Private Declare Function URLDownloadToFile Lib “urlmon” Alias “URLDownloadToFileA” (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long Public Function DownloadFile(URL As String, LocalFilename … Read more
With Access versions 2010 and later you can use an event-driven data macro to generate the sequential ID. For example, say you have a table named [poledata]. Open it in Design View and add two fields: alternate_id_seq – Numeric (Long Integer) alternate_id – Text(20) Save the changes to your table and then switch to Datasheet … Read more
I did some testing and there does seem to be some buffering going on with ACE OleDb. I found that If I did some INSERTs and then immediately did a SELECT on the same OleDbConnection the new rows were available right away. If I opened two OleDbConnection objects, INSERTed on con1, and then immediately SELECTed … Read more
Do not call the Module the same as the function, it can sometimes make things confusing for VBA. Rename the module something like “DatabaseUtils” for instance. Make sure the function is really defined as Public Function ConcatRelated(…, the Public here is important, otherwise the function will not be visible outside the module itself.
To print all connection strings: Dim tdf As TableDef Dim db As Database Set db = CurrentDb For Each tdf In CurrentDb.TableDefs If tdf.Connect <> vbNullString Then Debug.Print tdf.Name; ” — “; tdf.SourceTableName; ” — “; tdf.Connect End If Next To create a linked table: With CurrentDb ”If the table does not have a unique … Read more
Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strSql As String Set db = CurrentDb strSql = “UPDATE Month_Totals Set item_date = [which_date]” & _ ” WHERE id = [which_id];” Debug.Print strSql Set qdf = db.CreateQueryDef(vbNullString, strSql) With qdf .Parameters(“which_date”).Value = Date() .Parameters(“which_id”).Value = 1 .Execute dbFailOnError End With That example used a new, … Read more