[AccessD] Filesystemobject and sql server

[John Colby]

Kevin,

I don't think so but I am copying the list for other opinions.

When you convert to a SQL Server database, the data can be put in one or 
many different database files.  In any case, the SQL Server engine 
creates the database files and as long as they are mounted they are 
locked by the SQL Server engine.

1) The SQL Server engine can make copies of the database file(s) but the 
copies are in the SQL Server file formats.  It can also make backups of 
the files which are different from copies.

2) AFAIK no, the SQL Server engine cannot directly create a copy of the 
data in the MDB format.

The sql server engine can be manipulated by VBA.  It is very inefficient 
and I don't recommend it but it can be done.  I did so for awhile but I 
ended up learning C# and using that to do the SQL Server automation.
The problem with doing the automation in VBA is that VBA is single 
threaded which means that when a SQL Server operation begins, the thread 
in VBA is locked and control doesn't return to your app until SQL Server 
finishes whatever it is doing.  In C# you can have threads.  You can 
assign a thread to perform an operation, you can even spin off multiple 
threads and use each one to have the engine doing various things 
simultaneously.  And have a thread dedicated to your user interface so 
that the form (or whatever the UI is) doesn't lock up.

All that said, if all you want to do is run queries and reports from 
Access using SQL Server as a BE, that will work just fine within the 
restraints mentioned above.  If you ask for a stored procedure or query 
to hand back a data set, that will happen.  If it happens to take 10 
minutes your users will be frustrated because it appears that Access has 
locked up.  But it does work.  And the truth is that SQL Server often 
can do things in seconds that Access and JET would take minutes or hours 
to do natively.  So it is worth looking at / doing.  And sometimes a 
database just gets so big that the MDB route no longer works.

On 5/17/2018 12:08 AM, Kevin Roberts wrote:
> Hi John,
> I see your posts on the AccessD forum and I wonder if you know the 
> answer to my newbie questions:
> I have a split Access db both mdb format and use filesystemobject 
> Copyfile command in VBA to make a copy of the backend mdb.
> I'm planning to migrate the backend mdb to SQL server but keep the 
> Access front end. Will the filesystem object command still work after 
> migrating the backend to SQL server?
> 1.Can I use it to create a clone of the SQL server back end ?
> 2. Can I use it to convert the SQL server back-end tables to a copy of 
> the backend in the mdb format?
>
> Thanks
> Kevin
>
>
> Sent from ProtonMail mobile
>
>

-- 
John W. Colby