OpenDatabase with ODBC

[Marcelo]

Hi!

I have a Access Front End with linked tables to a ODBC SQL Server.

Before using a ODBC, this FE file had linked tables to an Access back end in
my machine, and untill that moment, I used the following code:

Sub Backup_Tables()
Set dbFE = CurrentDb()
strPath = dbFE.TableDefs("TR_WBS").Connect
strPath = Mid(strPath, 11)
Set dbFE = Nothing
Set dbBE = OpenDatabase(strPath)

dbBE.Execute "SELECT * INTO RESTORE_Period_Log FROM Period_Log"
End Sub

Now, with ODBC, I get an error on the line:

Set dbBE = OpenDatabase(strPath)

Error 3024, Could not find file name, where file name is:

ODBC;DSN=HRCST;Description=HR_Tool_Server;APP=2007 Microsoft Office
system;Network=DBMSSOCN;Address=10.20.136.20,1433

What am I doing wrong?

Thanks in advance, regards,
Marcelo

 

[Daryl S]

Marcelo -

This looks like you want to append a Period_Log table to another table for
backup/restore purposes. The code is looking for the path to a database
based on the connection string to the table "TR_WBS" when it was in Access.
The connection string had a path and filename, but now that your table is in
SQL Server, the connection string is an ODBC string, which of course is not a
path and filename that your PC can find.

You need to decide if you want to append the Period_Log records into a SQL
Server table or the old Access table. If it is in SQL Server, then you just
need to have the table RESTORE_Period_Log created in SQL Server, and use a
RunSQL command instead of a dbBE.Execute (and get rid of everything else in
the subroutine). If you want to append the Period_Log records to the old
Access Database, then you will need to provide Access with the path and
database name that you want to use. You can use the same logic here if you
have the front-end linked to a table in the same directory as the old
back-end. The TR_WBS table was used before, but if that is in SQL Server,
then you can use any table name. You may want to create a very small
back-end database that has only the one little table in it, and link it to
the front-end. Then replace the TR_WBS with the linked table name, and you
should be good to go.