SQL Server and Visual Basic

Remember when casting values that there are some cases where a null value will result in an error and no conversion. Check your properties for your variables in VB and you fields in SQL.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on SQL Server and Visual Basic

SQL or Access Adding Records

Suggestions on how to let users enter new data into SQL or even Access.

1) Build them a real data-entry app (could be web-based, but doesn’t have to
be) that will check all the inputs before submitting the data to the tables
OR
2) Have them input whatever they want (Excel – that could be
an option).
Import into a staging table with the same field names, but all data types
set to varchar(), so that there is no validation. This way you gather
everything the users send you.
Then write an app that goes through the staging table and does all the
validations that you want, imports the good records and spits out a report
of rejected records that can be dealt with manually

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on SQL or Access Adding Records

Referencing values on subforms

Remember the syntax for the way to recall information from a subform to a form is [subFormName].[Form]![FieldName]

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on Referencing values on subforms

VPN

Take advantage of free resources to create a VPN for remote access. LogMeIn is one such resource. Use it to create a VPN between your base location and any remote users you might have. That way you can remote into a database anytime with Internet access.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on VPN

Security

The first and most important step in creating a security process is to establish and maintain a solid backup process for ALL files! Whether they are only in Access or in Access and SQL Server.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on Security

DLookup

Doing an If clause with DLookup genereally means usin a comparison of the lookup result to a value using the equals function. Most other functions do not tend to work well with DLookup and especially not the IF ISNULL structure.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on DLookup

File System Objects

Remember to look to use FSO’s such as FileCopy when you are trying to do things outside of Acces from within Access. File System Objects are readily available and require few skills to learn.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on File System Objects

List boxes not populating

If you are using list box(es) and they will not populate with values, check your references in the VBA Editor. Odds are that there is a missing reference to a DLL or two.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on List boxes not populating

CSV Importing

The solution for importing a CSV file is to link to it instead of importing it. Linking to it, all the fields are correctly formatted and populated. One problem to watch out for; commas in the data. So I am turning to the semicolon as the answer. I am looking for the web developer to output the csv delimited by semicolons. If that is done there is a solution to the errors and format problems of importing a csv file. Once it is linked, I will then do the updates of the records. The only other step is to establish a set path for the location of the csv file.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged | Comments Off on CSV Importing

Importing CSV files

Importing files can be tricky in MS Access. The application tries to set the property for each field and even with preset values you can still get data imported incorrectly. I don’t have a complete solution for this problem just yet but keep checking our posts and you will see one as soon as I have worked it out.

Posted in Access 2007 problems/solutions, Uncategorized | Tagged , | Comments Off on Importing CSV files