Updated to Access 2007 Code no longer works

[GA]

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
oApp.CloseCurrentDatabase

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.

 

[Stefan Hoffmann]

hi,

This is really jamming things up. I am not a programmer and have been tasked
with 'fixing' the add-in.

Read this first as you really need to unterstand it:

http://www.aspfree.com/c/a/Database/Data-Access-from-Excel-VBA/2/

Instead of automating Access use the DAO access method.
Use db.Execute Sql, dbFailOnError instead of your DoCmd.RunSQL SQL
statements.


mfG
--> stefan <--