Upload Access DB to SQL Server in AccessVBA

Topic

New Reply

in the middle of figuring this one out, but i thought i’d pass it by the loungers…

What i want to do is, using Access, create a procedure that creates a new, archive-ready access db and load that created db into SQL Server. this would be a nice win as the archived dbs wouldn’t be hanging out in some file server somewhere. if it has to be written to a directory first, that’s ok – i’d just build in some delete routine.

I’ve previously implemented file uploading in ASP so there’s probably a solution out there. the process should be invisible to the user and would happen based on some user action. (Actually, a lot of things will happen when the button is clicked.)

Reply | Quote

Replies

in the middle of figuring this one out, but i thought i’d pass it by the loungers…

What i want to do is, using Access, create a procedure that creates a new, archive-ready access db and load that created db into SQL Server. this would be a nice win as the archived dbs wouldn’t be hanging out in some file server somewhere. if it has to be written to a directory first, that’s ok – i’d just build in some delete routine.

I’ve previously implemented file uploading in ASP so there’s probably a solution out there. the process should be invisible to the user and would happen based on some user action. (Actually, a lot of things will happen when the button is clicked.)

Steve, why not let SQL Server to the donkey work, after all that is what it is built for? Couldn’t you just create a DTS package that is able to pick up your access database tables and import them into the server. Just thoughts

Reply | Quote

I want to keep this all in Access as that is the primary (actually only) UI for the project. I also want to allow people who don’t necessarily want to deal with anything more than clicking a button to manage the entire process under consideration, so no SQL DTS runs, if I can help it…

I suppose I could create a button in Access that runs some DTS package on SQL Server – that would be fine. Not sure how to do that, tho.

Reply | Quote

Just to help my understanding is it an Access front end with a SQL back end?

Reply | Quote

not even that much – it’s an access project with forms and VBA – no data.

Reply | Quote

OK – now I’m confused. From your description I think you have an ADP which connects directly to a SQL Server database. So why do you want to create a new access database in SQL Server? Access databases, whether they are .mdb, .mde, .adp, .ade, .accdb, or .accdp, are all stored as files in a directory systems of some sort. I suppose in theory, you could store an access database in a binary object field in SQL Server, but I don’t see any advantage in doing that, as it has to be a file in order to use it….

Reply | Quote

i know this discussion has moved to another thread, but in case anyone is wondering how to do it, here’s a way:

1. setup an ODBC connection to SQL Server
2. create a pass-thru query
3. before writing the query, access Properties to specify the ODBC file, which is basically a connection string. You can opt not to store the password in the query (it will be stored in plaintext…). If you don’t want a response (that is, a recordset), set Return Records to “No”.
4. write something like this:

INSERT into dbo.()
SELECT * FROM OPENROWSET(BULK N’\<path’, SINGLE_BLOB) as Document;

NOTE: if SQL Server is not running on the same machine as the target file, you must use UNC.

Reply | Quote

Hi Steve, could you explain the meaning of TableFieldSetToVarchar(Max)?

i know this discussion has moved to another thread, but in case anyone is wondering how to do it, here’s a way:

1. setup an ODBC connection to SQL Server
2. create a pass-thru query
3. before writing the query, access Properties to specify the ODBC file, which is basically a connection string. You can opt not to store the password in the query (it will be stored in plaintext…). If you don’t want a response (that is, a recordset), set Return Records to “No”.
4. write something like this:

INSERT into dbo.()
SELECT * FROM OPENROWSET(BULK N’\<path’, SINGLE_BLOB) as Document;

NOTE: if SQL Server is not running on the same machine as the target file, you must use UNC.

Reply | Quote

Hi Steve, could you explain the meaning of TableFieldSetToVarchar(Max)?

just specifying that the column data type where you store the db should be Varchar(max)

Reply | Quote

Note that the Varchar(max) works in SQL Server 2005 and 2008, but in 2000 the limit for varchars is 8000 bytes and Access databases won’t fit in that case. However the text and image types could be used, as they generally support up to about 2GB.

Reply | Quote

I believe IMAGE was (or is) an option in 2000 for binary data.

Reply | Quote