access database error

[arjun.bhandari]

Hi,

I am new to the use of access. I have defined the database in the dsn and named that 'abexp', now when i go to access open the database and then create a module and try to establish a connection to the abexp, it gives me an error - database has been placed in a state by an unknown user that prevents it from being opened or locked. Can someone tell me what am i doing that is incorrect over here. i am trying to get convert from sql server to access.

Best Regards,
Arjun Bhandari

 

[boblarson]

So, abexp is your ODBC connection to the SQL Database?

 

[arjun.bhandari]

yes that is what is defined in the control panel->administrative tools->data sources, and it uses the microsoft access driver

 

[boblarson]

Okay, that confused me more. I asked if that was an ODBC connection (which we both agree on) to an SQL Database (meaning SQL Server) and you said it uses the Microsoft ACCESS driver. You can't use an Access driver to connect up to an SQL Server Database. It has to be the SQL Server driver.

Are you sure that you aren't creating an Access ODBC connection, to an Access database, and you are trying to connect to the actual Access database that you are already in? If you are putting code in the module of the database that you are already in, and it is the one you are attempting to use the ODBC connection with, then of course it is locked as you are in it already. You can just use (with Access 2000 or above) the CurrentProject.Connection as your connection in your connection string.

 

[arjun.bhandari]

Thanks, I guess that was what I was doing. I was looking for something equivalent to stored procedures in SQL to be executed in access, and hence tried to write the visual basic procedures. What would be an equivalent of a stored procedure in sql to something in access. i want to look through all the records and replace all the NA with null datatype. Thanks.