Updated to Access 2007 Code no longer works



I am wondering if someone can help figure out why this code is no longer
working. We recently upgradeed to Office 2007 and one of our Excel Add-ins no
longer functions. It is supposed to take a column of numbers from excel
table, open an access database (in the same folder with same name), and make
a table in that DB. Here is a portion of the code that it gets stuck on. I am
a complete novice and would appreciate any insight.

Dim strTargetDatabaseFilename As String
strTargetDatabaseFilename = Replace(Application.ActiveWorkbook.FullName,
".xls", "")
Application.StatusBar = "Creating Blank Access Database " &
strTargetDatabaseFilename & ".mdb ..."
CreateNewAccess2000MDB strTargetDatabaseFilename
Dim oApp As Object
Set oApp = CreateObject("Access.Application")
oApp.OpenCurrentDatabase strTargetDatabaseFilename & ".mdb"
Application.StatusBar = "Creating Table [census] and Field [zip]..."
DoCmd.RunSQL "create table census (zip text (5))"

***The line above is where it gets hung up on ***

Dim i As Integer, iCount As Integer, iRows As Integer, iRecords As Integer
iRecords = 0
iRows = rng.Cells.Count
For i = 1 To iRows
iRecords = iRecords + 1
oApp.DoCmd.RunSQL "INSERT INTO census ( zip ) values ( '" & rng(i) &
"' ) "
Next i

This is really jamming things up. I am not a programmer and have been tasked
with 'fixing' the add-in. Thank you in advance for your help.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question