data does not 'save' in sql server back end

[Ian Burton]

Hi,
I have an access front end connected to a SQL Server back end. I run a
series of update queries, which take data from a few access tables, and
run append queries into SQL Server. this process can take a couple of
hours of quite intensive query activity each time to run about a hundred
queries, some with code functions from vba inside.

The functions all seem to run fine, and I can see all the appended data
inside the database. However, If I close the access database down, and
reopen it again, ALL the SQL SERVER data just appended has gone, almost
as if a transaction has been rolled back.

I would appreciate any clues as to where I might look for a solution. I
just have no ideas why the data would appear to be there. The reason I
close down the database is because if I go to a query I have not touched
to view some other data I get an ODBC error.

I am trapping errors, but I do use the line Docmd.setwarnings false,
because I am running append and update queries. I do not get any vba
errors though.

Is there some type of function I can run that will close down any
connections that might be causing a lock or something? then I can use
this function between each pass of the database or something?

Any clues gratefully received.
Thanks,
Ian Burton

 

[Ian Burton]

Hi,
I have an access front end connected to a SQL Server back end. I run a
series of update queries, which take data from a few access tables, and
run append queries into SQL Server. this process can take a couple of
hours of quite intensive query activity each time to run about a hundred
queries, some with code functions from vba inside.

The functions all seem to run fine, and I can see all the appended data
inside the database. However, If I close the access database down, and
reopen it again, ALL the SQL SERVER data just appended has gone, almost
as if a transaction has been rolled back.

I would appreciate any clues as to where I might look for a solution. I
just have no ideas why the data would appear to be there. The reason I
close down the database is because if I go to a query I have not touched
to view some other data I get an ODBC error.

I am trapping errors, but I do use the line Docmd.setwarnings false,
because I am running append and update queries. I do not get any vba
errors though.

Is there some type of function I can run that will close down any
connections that might be causing a lock or something? then I can use
this function between each pass of the database or something?

Any clues gratefully received.
Thanks,
Ian Burton

Hi, as a follow up,
the error code I get from Microsoft Access for the ODBC is:
Run-time error 3146
ODBC - call failed
thanks again in advance for any iassistance.
Ian Burton

 

[Guest]

Hi,
I have an access front end connected to a SQL Server back end. I run a
series of update queries, which take data from a few access tables, and
run append queries into SQL Server. this process can take a couple of
hours of quite intensive query activity each time to run about a hundred
queries, some with code functions from vba inside.

The functions all seem to run fine, and I can see all the appended data
inside the database. However, If I close the access database down, and
reopen it again, ALL the SQL SERVER data just appended has gone, almost
as if a transaction has been rolled back.

I would appreciate any clues as to where I might look for a solution. I
just have no ideas why the data would appear to be there. The reason I
close down the database is because if I go to a query I have not touched
to view some other data I get an ODBC error.

I am trapping errors, but I do use the line Docmd.setwarnings false,
because I am running append and update queries. I do not get any vba
errors though.

Is there some type of function I can run that will close down any
connections that might be causing a lock or something? then I can use
this function between each pass of the database or something?

Any clues gratefully received.
Thanks,
Ian Burton