Access
To determine the ODBC connection and query strings, set query options manually, and use the QueryString property to return the connection string. The following table includes some commonly used SQL keywords.
Keyword Description
DSN The name of the ODBC data source
UID The user logon ID
PWD The user-specified password
DBQ The database file name
FIL The file type
Connection String - Access
This is the previous connection string
Provider -
Data Source - DSN alternative
UID -
PWD -
Persist Security Info -
Driver -
DBQ -
SystemDB -
Exclusive =
UID -
PWD -
ExtendedAnsiSQL -
LocaleIdentifier -
Access 2007 provides two-way snychronisation between Access and SharePoint lists so you can work with lists offline and then synchronise the changes when you reconnect.
Integration with WSS makes Access 2007 a great colloboration tool while enablying data to be stored on enterprise servers to better manageability
Access 2007 allows you to export and import data to and from SharePoint lists
In this scenario changes made in Access 2007 are not reflected in SharePoint and vica versa
Exporting Tables To SharePoint
select the table
select (External Data tab, Export)(SharePoint List)
A "Move to SharePoint Site Issues" table appears in the Access navigation pane that details aspects of the data that could not be applied to the sharepoint list
By saving the export query you can now repeat the above steps without using the wizard
Your saved exports can be found under the External Data tab in the Export group
Similarly you can save your export as a Microsoft Outlook task which you can then configure to remind you to run the export query.
Importing Lists to Access
External Data tab
Import > SharePoint list
The following options are available on a dialog box
Not Linked !!
Linked !!
When you import data from a SharePoint list, the imported data are based on a view and only those columns and rows in the view are imported
You can select the required view from the Items to Import drop-down list
The Import displays values instead of IDs for lookup values stored in another list and allows you to maintain the lookup relationship that this list may have with other sharepoint lists.
Changes to the sharepoint list are NOT copied back to the Access table nor are changes to the Access database reflected back in the sharepoint list.
A linked object is not created as part of this process.
Linking Lists
If you do not want to maintain two copies of that data but do need to refer to the data within Access then Access 2007 provides methods of accessing external data that are physically located outside an Access database
The easiest way to externally reference a SharePoint lists is to use linked tables
A linked table stores only a connection to the sharepoint list
You should use linking rather than importing if the data are maintained by either a user or a separate application on the sharepoint web site.
Check that the "Link to the data source by creating a linked table" option is selected and click Next
The Get External Data - sharepoint list dialog box closes
Access now has two linked tables
The sharepoint list contains columns "Created by" and "Modified by"
These lookup columns point to the User Information List ???
A context sensitive menu is displayed that provides a quick way to manage the sharepoint list from within Access.
By moving to another row, Access automatically synchronises changes to the sharepoint list.
F5 - to refresh
Moving Tables To SharePoint
You can move a database to a sharepoint site
Data is moved to sharepoint lists and the database file is moved to a document library.
Many Access applications grow from the need to manage and aggregate data
These data-centric applications often prove useful to more than one person in an organisation and thus the need to share them increases.
However Access 2007 is not truly meant for concurrent use
As an Access database grows and becomes more complex, it is necessary to consider upsizing them to a data repository that can support more users while increasing availablity, reliability and manageability
With Access 2007 you can now upsize your database to Windows SharePoint Services which is known as "Publishing your database"
When you move data from an Access database to a sharepoint site this process creates a sharepoint list for each Access table
Data from Access tables move into these sharepoint lists and each data row becomes a list item in a sharepoint list
Tables in the Access database are replaced with linked tables that point to the newly created sharepoint list(s)
Optionally the Access database now becomes a user interface to the data by retaining views, reports and relationships between tables
Access views also appear as sharepoint list views
Because the data is now in sharepoint you can use sharepoint functionality
For example you can restore deleted list items from the Recycle Bin and apply workflow rules to data items
Changes to the list are versioned by defaukt
If you choose to save the database in the document library, users who wish to use the database can navigate to the document library in their browser, where the database can be opened in Access
Access 2007 users can either add and modify data by using either sharepoint or the linked tables within an Access database
External Data tab
SharePoint lists > Move to sharepoint
Other changes, file can be treated as a document - check in / check out changes
If you make changes to the Access database, such as creating a new biew or report then the database file can be saved to the document library on the server by publishing changes.
Access Functions
| Function | Description |
| ABS | Returns a value of the same type that is passed to it specifying the absolute value of a number. |
| APPACTIVATE | |
| ARRAY | Returns a Variant containing an array. (not in object browser) |
| ASC | Returns an Integer representing the character code corresponding to the first letter in a string. |
| ATN | Returns a Double specifying the arctangent of a number. |
| BEEP | |
| CALLBYNAME | Executes a method of an object, or sets or returns a property of an object. |
| CBOOL | Returns the expression converted to a boolean data type. |
| CBYTE | Returns the expression converted to a byte data type. |
| CCUR | |
| CDATE | Returns the expression converted to a date data type. |
| CDBL | Returns the expression converted to a double data type. |
| CDEC | Returns a Variant (Decimal) containing the expression converted to a decimal. |
| CHDIR | |
| CHDRIVE | |
| CHR | |
| CHOOSE | Selects and returns a value from a list of arguments. |
| CINT | Returns the expression converted to an integer data type. |
| CLNG | Returns the expression converted to a long data type. |
| CLNGLNG | |
| CLNGPTR | |
| COMMAND | |
| COS | Returns a Double specifying the cosine of an angle. |
| CREATEOBJECT | |
| CSNG | |
| CSTR | |
| CURDIR | Returns a Variant (String) representing the current path. |
| CVAR | |
| CVDATE | |
| CVERR | |
| DATE | |
| DATEADD | Returns a Variant (Date) containing a date to which a specified time interval has been added. |
| DATEDIFF | Returns a Variant (Long) specifying the number of time intervals between two specified dates. |
| DATEPART | Returns a Variant (Integer) containing the specified part of a given date. |
| DATESERIAL | Returns a Variant (Date) for a specified year, month, and day. |
| DATEVALUE | Returns a Variant (Date). |
| DAVG | |
| DAY | Returns a Variant (Integer) specifying a whole number between 1 and 31, inclusive, representing the day of the month. |
| DCOUNT | (backwards compatibility) |
| DDB | Returns a Double specifying the depreciation of an asset for a specific time period by using the double-declining balance method or some other method you specify. |
| DDEINITIATE | (backwards compatibility) |
| DDEREQUEST | (backwards compatibility) |
| DELETESETTING | |
| DFIRST | (backwards compatibility) |
| DIR | Returns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive. |
| DLAST | (backwards compatibility) |
| DLOOKUP | (backwards compatibility) |
| DMAX | (backwards compatibility) |
| DMIN | (backwards compatibility) |
| DOEVENTS | Yields execution so that the operating system can process other events. |
| DSTDEV | (backwards compatibility) |
| DSTDEVP | (backwards compatibility) |
| DSUM | (backwards compatibility) |
| DVAR | (backwards compatibility) |
| DVARP | (backwards compatibility) |
| ENVIRON | Returns the String associated with an operating system environment variable. Not available on the Macintosh. |
| EOF | Returns an Integer containing the Boolean value True when the end of a file opened for Random or sequential Input has been reached. |
| ERR | |
| ERROR | Returns the error message that corresponds to a given error number. |
| EUROCONVERT | (backwards compatibility) |
| EVAL | (backwards compatibility) |
| EXP | |
| FILEATTR | Returns a Long representing the file mode for files opened by using the Open statement. |
| FILECOPY | |
| FILEDATETIME | Returns a Variant (Date) that indicates the date and time when a file was created or last modified. |
| FILELEN | Returns a Long specifying the length of a file in bytes. |
| FILTER | Returns a zero-based array containing a subset of a string array based on a specified filter criteria. |
| FIX | |
| FORMAT | |
| FORMATCURRENCY | Returns an expression formatted as a currency value by using the currency symbol defined in the system control panel. |
| FORMATDATETIME | Returns an expression formatted as a date or time. |
| FORMATNUMBER | Returns an expression formatted as a number. |
| FORMATPERCENT | Returns an expression formatted as a percentage (multipled by 100) with a trailing % character. |
| FREEFILE | Returns an Integer representing the next file number available for use by the Open statement. |
| FV | Returns a Double specifying the future value of an annuity based on periodic fixed payments and a fixed interest rate. |
| GETALLSETTINGS | Returns a list of key settings and their respective values (originally created with SaveSetting) from an application's entry in the Windows registry or (on the Macintosh) information in the application's initialization file. |
| GETATTR | Returns an Integer representing the attributes of a file, directory, or folder. |
| GETOBJECT | Returns a reference to an object provided by an ActiveX component. |
| GETSETTING | Returns a key setting value from an application's entry in the Windows registry or (on the Macintosh) information in the application's initialization file. |
| GUIDFROMSTRING | (backwards compatibility) |
| HEX | |
| HOUR | Returns a Variant (Integer) specifying a whole number between 0 and 23, inclusive, representing the hour of the day. |
| IIF | Returns one of two parts, depending on the evaluation of an expression. |
| IMESTATUS | Returns an Integer specifying the current Input Method Editor (IME) mode of Microsoft Windows; available in East Asian versions only. |
| INPUTBOX | Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns a String containing the contents of the text box. |
| INSTR | Returns a Variant (Long) specifying the position of the first occurrence of one string within another. |
| INSTRREV | Returns the position of an occurrence of one string within another, from the end of the string. |
| INT | |
| IPMT | Returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate. |
| IRR | Returns a Double specifying the internal rate of return for a series of periodic cash flows (payments and receipts). |
| ISARRAY | Returns a Boolean value indicating whether a variable is an array. |
| ISDATE | Returns True if the expression is a date or is recognizable as a valid date or time; otherwise, it returns False. |
| ISEMPTY | Returns a Boolean value indicating whether a variable has been initialized. |
| ISERROR | Returns a Boolean value indicating whether an expression is an error value. |
| ISMISSING | Returns a Boolean value indicating whether an optional Variant argument has been passed to a procedure. |
| ISNULL | Returns a Boolean value that indicates whether an expression contains no valid data (Null). |
| ISNUMERIC | Returns a Boolean value indicating whether an expression can be evaluated as a number. |
| ISOBJECT | Returns a Boolean value indicating whether an identifier represents an object variable. |
| JOIN | Returns a string created by joining a number of substrings contained in an array. |
| KILL | |
| LCASE | Returns a String that has been converted to lowercase. |
| LEFT | Returns a Variant (String) containing a specified number of characters from the left side of a string. |
| LEN | Returns a Long containing the number of characters in a string or the number of bytes required to store a variable. |
| LOC | Returns a Long specifying the current read/write position within an open file. |
| LOF | Returns a Long representing the size, in bytes, of a file opened by using the Open statement. |
| LOG | Returns a Double specifying the natural logarithm of a number. |
| LTRIM | Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim). |
| MACSCRIPT | (backwards compatibility) |
| MID | Returns a Variant (String) containing a specified number of characters from a string. |
| MINUTE | Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the minute of the hour. |
| MIRR | Returns a Double specifying the modified internal rate of return for a series of periodic cash flows (payments and receipts). |
| MONTH | Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year. |
| MONTHNAME | Returns a string indicating the specified month. |
| MSGBOX | Displays a message in a dialog box, waits for the user to click a button, and returns an Integer indicating which button the user clicked. |
| NOW | Returns a Variant (Date) specifying the current date and time according to your computer's system date and time. |
| NPER | Returns a Double specifying the number of periods for an annuity based on periodic, fixed payments and a fixed interest rate. |
| NPV | Returns a Double specifying the net present value of an investment based on a series of periodic cash flows (payments and receipts) and a discount rate. |
| NZ | (backwards compatibility) |
| OCT | |
| PARTITION | Returns a Variant (String) indicating where a number occurs within a calculated series of ranges. |
| PMT | Returns a Double specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate. |
| PPMT | Returns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate. |
| PV | Returns a Double specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate. |
| QBCOLOR | Returns a Long representing the RGB color code corresponding to the specified color number. |
| RANDOMIZE | |
| RATE | Returns a Double specifying the interest rate per period for an annuity. |
| REPLACE | Returns a string, which is a substring of a string expression beginning at the start position (defaults to 1), in which a specified substring has been replaced with another substring a specified number of times. |
| RESET | |
| RGB | Returns a Long whole number representing an RGB color value. |
| RIGHT | Returns a Variant (String) containing a specified number of characters from the right side of a string. |
| RMDIR | |
| RND | |
| ROUND | Returns a number rounded to a specified number of decimal places. |
| RTRIM | Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim). |
| SAVESETTING | |
| SECOND | Returns a Variant (Integer) specifying a whole number between 0 and 59, inclusive, representing the second of the minute. |
| SEEK | Returns a Long specifying the current read/write position within a file opened by using the Open statement. |
| SENDKEYS | |
| SETATTR | |
| SGN | |
| SHELL | Runs an executable program and returns a Variant (Double) representing the program's task ID if successful; otherwise, it returns zero. |
| SIN | |
| SLN | Returns a Double specifying the straight-line depreciation of an asset for a single period. |
| SPACE | Returns a Variant (String) consisting of the specified number of spaces. |
| SPC | Used with the Print # statement or the Print method to position output. |
| SPLIT | Returns a zero-based, one-dimensional array containing a specified number of substrings. |
| SQR | |
| STR | |
| STRCOMP | Returns a Variant (Integer) indicating the result of a string comparison. |
| STRCONV | Returns a Variant (String) converted as specified. |
| STRING | Returns a Variant (String) containing a repeating character string of the length specified. |
| STRINGFROMGUID | (backwards compatibility) |
| STRREVERSE | Returns a string in which the character order of a specified string is reversed. |
| SWITCH | Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True. |
| SYD | Returns a Double specifying the sum-of-years' digits depreciation of an asset for a specified period. |
| TAB | Used with the Print # statement or the Print method to position output. |
| TAN | |
| TIME | Returns a Variant (Date) indicating the current system time. |
| TIMER | Returns a Single representing the number of seconds elapsed since midnight. |
| TIMESERIAL | Returns a Variant (Date) containing the time for a specific hour, minute, and second. |
| TIMEVALUE | Returns a Variant (Date) containing the time. |
| TRIM | Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim). |
| TYPENAME | Returns a String that provides information about a variable. |
| UCASE | Returns a Variant (String) containing the specified string, converted to uppercase. |
| UNLOAD | |
| VAL | |
| VARTYPE | Returns an Integer indicating the subtype of a variable, or the type of an object's default property. |
| WEEKDAY | Returns a Variant (Integer) containing a whole number representing the day of the week. |
| WEEKDAYNAME | Returns a string indicating the specified day of the week. |
| YEAR | Returns a Variant (Integer) containing a whole number representing the year. |
© 2026 Better Solutions Limited. All Rights Reserved. © 2026 Better Solutions Limited TopPrevNext