Web Development

General discussion


Access Macro to populate SQL database

By c-mackenzie ·

I have an Access 2000 database(auto retrieve.mdb) that has a macro in it which runs automatically when the file is opened and amongst other tasks populates another access 2000 database. Recently I've upsized this "other" database to SQL Server 2000 and I am hoping to change the macro in the auto retrieve.mdb to populate the SQL database as opposed to the "other" access database.

I've tried converting the auto retrieve db to 2002 - 2003 and changing the action in the macro to 'transferSQLdatabase' as opposed to 'transferdatabase' but I still receive read only errors and unconverted database errors when I run the macro. in the transferSQLdatabase action I've checked Yes for use a trusted connection, do i also need to put in a username and password for the server I intend to update to ?

Any help is much appreciated


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by Tony Hopkinson In reply to Access Macro to populate ...

can you set up- an external link to the database within access ?
When you are running access, what windows user are you. Does the server sql server is on 'know' about this user.
As a test check you can see the datbase from access with the sa login. Default install this will be the administrator account that installed sql server.
If it's not or you can't or don't want to use admin. once you've verified that the server with sql server is on recognises the windows user that you are running access under. you need to add a login to the server (user SQL Enterprise, it's under teh secutity node. and map it to tho a know user of teh database in question. you can use dbo or create your own at assign a set of privileges.


PS if all this gets to be a PIA, you can though it's not recomended swith the sql server to a mixed or sql only secutity model. your user names and passwords will no longer be integreated and you'll have to manually setup logins and passwords and map them to users.

Collapse -

by c-mackenzie In reply to

Poster rated this answer.

Collapse -

by bschaettle In reply to Access Macro to populate ...

Try creating an ODBC connection to the SQL Server database. In the connection string, use a SQL Login that has appropriate read/write permissions on the SQL tables. Then use that ODBC connection to populate the SQL Server database from Access. Just an idea, don't know if it will work...

Collapse -

by c-mackenzie In reply to

I've gone down the route of automating the macros in Access using HAL.

Thanks for your reocmmendations though.

Best regards


Collapse -

by c-mackenzie In reply to Access Macro to populate ...

This question was closed by the author

Related Discussions

Related Forums