EzDevInfo.com

VBA check if object is set

I have a global variable that is an instance of my custom class.

How do I check if the object is set or if I need to initialize it?

Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb

It has been a good number of years since I did some programming with Classic ASP and Microsoft Access where we used "Microsoft.Jet.Oledb" driver to access and display the data.

I have been asked to do some work with accessing MS Excel data using "Microsoft.Ace.Oledb". I have found this to be part of the "Microsoft Access 2010 Engine Redistributable" download.

I would like to know if "Microsoft.Jet.OleDb" has replaced "Microsoft.Ace.Oledb" driver and are essentially the same or are they completely different things?

Also, do you normally get the "Microsoft.Ace.Oledb" driver when you buy MS Access 2010?

What will we do after Access? [closed]

Microsoft seems hell-bent on deprecating the swiss-army-knife of database tools. What else comes close for facading/file-swapping/cloning/name-your-acronym-connecting arbitrary database servers/spreadsheets/CSV's/flatfiles?

What weird kinds of functionality have you squeezed out of Access? And what else is there to take its place?

Best way to test a MS Access application?

With the code, forms and data inside the same database I am wondering what are the best practices to design a suite of tests for a Microsoft Access application (say for Access 2007).

One of the main issues with testing forms is that only a few controls have a hwnd handle and other controls only get one they have focus, which makes automation quite opaque since you cant get a list of controls on a form to act on.

Any experience to share?

Query Microsoft Access MDB Database using LINQ and C#

I have a *.MDB database file, and I am wondering if it is possible or recommended to work against it using LINQ in C#. I am also wondering what some simple examples would look like.

I don't know a lot about LINQ, but my requirements for this task are pretty simple (I believe). The user will be passing me a file path to Microsoft Access MDB database and I would like to use LINQ to add rows to one of the tables within the database.

Why is "Yes" a value of -1 in MS Access database?

I'm looking at linked data in MS Access.

The "Yes/No" fields contain the value -1 for YES and 0 for NO. Can someone explain why such a counter-intuitive value is used for "Yes"? (Obviously, it should be 1 and 0)

I imagine there must be a good reason, and I would like to know it.

Access VBA | How to replace parts of a string with another string

I am trying to create a piece of code that replaces one word with another. Example: Replace Avenue with Ave and North with N. I am using MS Access, I could use SQL REPLACE Function but I want to do this in VBA using Access module so that I can attached the function to other column.

I am not sure where to start with this, so any input will be greatly appreciated.

Guy

How do you use version control with Access development?

I'm involved with updating an Access solution. It has a good amount of VBA, a number of queries, a small amount of tables, and a few forms for data entry & report generation. It's an ideal candidate for Access.

I want to make changes to the table design, the VBA, the queries, and the forms. How can I track my changes with version control? (we use Subversion, but this goes for any flavor) I can stick the entire mdb in subversion, but that will be storing a binary file, and I won't be able to tell that I just changed one line of VBA code.

I thought about copying the VBA code to separate files, and saving those, but I could see those quickly getting out of sync with what's in the database.

How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want some query like this:

INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES
  (SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1);

I tried but get a syntax error message.

What would you do if you want to do this?

Good Free Alternative To MS Access [closed]

Consider the need to develop a lightweight desktop DB application on the Microsoft platforms.

It could be done fairly easily with MS Access but I'd like to be able to distribute it to others and I don't want to pay for a runtime license.

Requirements:

• easy distribution to others
• no runtime licensing issues

Considerations and Candidates:

• Base from the OpenOffice suite. My concerns were around its stability.
• MySQL + writing custom DB code in C++ or Python or whatever seems like a rather heavy-handed solution.

Question: What are the low cost or free database alternatives to MS Access?

See Also: Open Source Reporting Engines

@Schnapple

Bruceatk kind of hit on what I'm thinking of; it's not so much the DB engine as I want the other niceties that Access brings to the party. The nice form designer, the nice reporting engine etc. But you do raise a very good point about the installation footprint. I had considered that but I've not made any firm decisions about which way I'm going with this yet anyway. It'll probably be something fairly lightweight anyway and a small installation footprint would definitely be a plus.

@Remou,

No I was unaware that the MS Access 2007 runtime is free; thanks for pointing that out. The last time I'd bothered to investigate it (I don't remember when it was) I think it was a fairly expensive license for the runtime because I think they were trying to sell it to Corporate IT departments.

And thanks to everyone else who responded as well; I was completely unaware of those other options you all pointed out.

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)

Lightweight SQL database which doesn't require installation

Could you recommend a lightweight SQL database which doesn't require installation on a client computer to work and could be accessed easily from .NET application? Only basic SQL capabilities are needed.

Now I am using Access database in simple projects and distribute .MDB and .EXE files together. Looking for any alternatives.

How can I modify a saved Microsoft Access 2007 or 2010 Import Specification?

Does anyone know how to modify an existing import specification in Microsoft Access 2007 or 2010? 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 else.

Microsoft.ACE.OLEDB.12.0 provider is not registered

I have a Visual Studio 2008 solution with two projects (a Word-Template project and a VB.Net console application for testing). Both projects reference a database project which opens a connection to an MS-Access 2007 database file and have references to System.Data.OleDb. In the database project I have a function which retrieves a data table as follows

 private class AdminDatabase
   ' stores the connection string which is set in the New() method
   dim strAdminConnection as string

   public sub New()
   ...
   adminName = dlgopen.FileName
   conAdminDB = New OleDbConnection
   conAdminDB.ConnectionString = "Data Source='" + adminName + "';" + _
       "Provider=Microsoft.ACE.OLEDB.12.0"

   ' store the connection string in strAdminConnection
   strAdminConnection = conAdminDB.ConnectionString.ToString()
   My.Settings.SetUserOverride("AdminConnectionString", strAdminConnection)
   ...
   End Sub

   ' retrieves data from the database
   Public Function getDataTable(ByVal sqlStatement As String) As DataTable
        Dim ds As New DataSet
        Dim dt As New DataTable
        Dim da As New OleDbDataAdapter
        Dim localCon As New OleDbConnection


        localCon.ConnectionString = strAdminConnection

        Using localCon
            Dim command As OleDbCommand = localCon.CreateCommand()
            command.CommandText = sqlStatement
            localCon.Open()
            da.SelectCommand = command
            da.Fill(dt)
            getDataTable = dt
        End Using

    End Function
End Class

When I call this function from my Word 2007 Template project everything works fine; no errors. But when I run it from the console application it throws the following exception

ex = {"The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."}

Both projects have the same reference and the console application did work when I first wrote it (a while ago) but now it has stopped work. I must be missing something but I don't know what. Any ideas?

Manipulating an Access database from Java without ODBC

I want to manipulate a Microsoft Access database (.accdb or .mdb file) from my Java project. I don't want to use the JDBC-ODBC Bridge and the Access ODBC driver from Microsoft because:

• the JDBC-ODBC Bridge has been removed from Java SE 8 and is not supported (ref: here),
• the JDBC-ODBC Bridge does not work properly with the Access ODBC driver when text includes Unicode characters with code points above U+00FF (ref: here), so such a setup would not be able to handle characters such as Greek, Russian, Chinese, Arabic, etc.,
• the Access ODBC driver from Microsoft only works in Windows, and
• there are separate 32-bit and 64-bit versions of the Access Database Engine (and ODBC driver) which can be a nuisance for deployment.

I have seen other answers mentioning a JDBC driver for Access databases named UCanAccess. How can I set up my Java project to use this approach?

(Answers suggesting better ways of working with Access databases from Java would also be most welcome.)