Error writing to SQL database from Access

[djharrison]

Greetings,

I am developing an application that writes values from an Access 2000
database to an SQL 7.0 server using DAO and linked tables. It seems to
work fine for the first 26 of 108 tables, but part way through the
population of the 27th table it stops and displays a 'Runtime Error
3001 Invalid Arguement' message. Not sure what to make of this.

Any help would be appreciated.

Daryl

For nZone = 1 To p_intZoneCount
If p_bRunZones(nZo ne) Then
Debug.Print "Writing zone " & nZone & "..."
With p_zneData(nZone )
nMaxRow = .MaxRow - .MinRow
nMaxCol = .MaxCol - .MinCol
For nRow = .MinRow To .MaxRow
For nCol = .MinCol To .MaxCol
rstData.AddNew
rstData!ddRow = nRow
rstData!ddcol = nCol
For nStep = 0 To SCN_STEPS
rstData("ddDens ity" & nStep) =
p_intMapData(nS tep, nRow, nCol)
Next nStep
rstData.Update <<BONKS HERE>>
Next nCol
Next nRow
End With
End If
Next nZone

[MGFoster]

Re: Error writing to SQL database from Access

djharrison wrote:
[color=blue]
> Greetings,
>
> I am developing an application that writes values from an Access 2000
> database to an SQL 7.0 server using DAO and linked tables. It seems to
> work fine for the first 26 of 108 tables, but part way through the
> population of the 27th table it stops and displays a 'Runtime Error
> 3001 Invalid Arguement' message. Not sure what to make of this.
>
> Any help would be appreciated.
>
> Daryl
>
> For nZone = 1 To p_intZoneCount
> If p_bRunZones(nZo ne) Then
> Debug.Print "Writing zone " & nZone & "..."
> With p_zneData(nZone )
> nMaxRow = .MaxRow - .MinRow
> nMaxCol = .MaxCol - .MinCol
> For nRow = .MinRow To .MaxRow
> For nCol = .MinCol To .MaxCol
> rstData.AddNew
> rstData!ddRow = nRow
> rstData!ddcol = nCol
> For nStep = 0 To SCN_STEPS
> rstData("ddDens ity" & nStep) =
> p_intMapData(nS tep, nRow, nCol)
> Next nStep
> rstData.Update <<BONKS HERE>>
> Next nCol
> Next nRow
> End With
> End If
> Next nZone[/color]


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

My guess is that one of the values (nStep, nRow or nCol) is NULL or the
function p_intMapData() returns a value that is not allowed in the
column ddDensity (perhaps a NULL or a number [ddDensity is a numeric
column, isn't it?] that is out of range).

Remove all error handling from the procedure that contains your code.
Run the procedure again. When the error occurs, the VBA module window
should open. You can then use the Debug Window (or cursor-hover method)
to determine the values of the above named variables.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIXDx4echKq OuFEgEQL1GQCg/Un4HIUIeQ+S2bEW AVD1K/1lKuEAniuC
rMUOFAWTOR9Z/cXqsyEWk2Uk
=xqfF
-----END PGP SIGNATURE-----

[djharrison]

Re: Error writing to SQL database from Access

Thanks,

I tried using the cursor-hover method and found that all the variables
were numeric and had a value of 0, which are valid. I also was able to
run the algorithm for the particular offending table only and it ran
through completely just fine! There must be something up with the fact
that it encounters an error when run as part of the larger loop
through all 108 tables?

Any other suggestions are welcome.
Thanks again.

Daryl

[MGFoster]

Re: Error writing to SQL database from Access

The error "Invalid argument" error occurs when a function/sub is called.
Where in your code does the error occur?

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)


djharrison wrote:
[color=blue]
> Thanks,
>
> I tried using the cursor-hover method and found that all the variables
> were numeric and had a value of 0, which are valid. I also was able to
> run the algorithm for the particular offending table only and it ran
> through completely just fine! There must be something up with the fact
> that it encounters an error when run as part of the larger loop
> through all 108 tables?
>
> Any other suggestions are welcome.
> Thanks again.[/color]

[djharrison]

Re: Error writing to SQL database from Access

The error get thrown at 'rstData.Update '.
[color=blue]
> Next nStep
> rstData.Update <<BONKS HERE>>
> Next nCol[/color]

It runs through a number of records prior to throwing an error?

[MGFoster]

Re: Error writing to SQL database from Access

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I don't know what the cause of the error is. You might try
compiling/compacting the application & see if that fixes the problem.

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQI1d4IechKq OuFEgEQJ97ACeN+ 5juQ+CnhYUCQjX+ 5pj6P9yROsAoIfc
+U1h9F3bxCAIo7g n0zgX+LRQ
=gaCI
-----END PGP SIGNATURE-----


djharrison wrote:
[color=blue]
> The error get thrown at 'rstData.Update '.
>
>[color=green]
>> Next nStep
>> rstData.Update <<BONKS HERE>>
>> Next nCol[/color]
>
>
> It runs through a number of records prior to throwing an error?[/color]