Update code after moving database

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I am moving an access 97 database from our old server to our new server.
Before I update everyone to Office 2003 I wanted to try the database on my
computer. So I uninstall Office 97 and installed Office 2003 on my computer.
I made a copy of the database on my pc. I updated the copy to Office 2003
and re-established the linked database. I am now trying to change the
command button that we use to import data into the database. When I changed
the string to the new location on my computer always comes up with an
invalid path. Is there different syntax for 2003? I am trying to access a
file folder on my local computer not on our network. Any suggestions greatly
appreciated.

Thanks,
Terry
 
Hi Terry,

On the basis of that description all one can do is guess. Have you set a
breakpoint and stepped though the code to see where it fails, and
checked the actual path that's being used at that point?

If after doing that you can't spot the problem, post back here,
including the code and showing the line at which it fails.
 
Here is the code from the command button that we were using the only changes I made was to the line PathStr = "C:\CAMSTEST\"(this is the location on my pc for the trial data) . The previous code was PathStr = "F:\DBVDATA\" which was the the location on our old server. We are deleting three tables and importing the updated data from another database into our access database. I comment these lines out only when the code ran once and deleted the data and did not complete the process

strMsg = MsgBox("Are you sure you want to import data?", vbOKCancel
If strMsg = 1 Then 'comment this lin
DoCmd.DeleteObject acTable, "FAS" 'comment this lin
DoCmd.DeleteObject acTable, "FAT" 'comment this lin
DoCmd.DeleteObject acTable, "FRC" 'comment this lin

PathStr = "C:\CAMSTEST\
PathStr = DLookup("[PathStr]", "tblPathStr"

FileStr = "FAS.DBF
If FileDateTime(PathStr & "Fas.dbf") The
DoCmd.TransferDatabase acImport, "Foxpro 3.0", PathStr, acTable, "Fas.dbf", "FAS
End I

FileStr = "FAT.DBF
If FileDateTime(PathStr & "Fat.dbf") The
DoCmd.TransferDatabase acImport, "Foxpro 3.0", PathStr, acTable, "Fat.dbf", "FAT
End I

FileStr = "FRC.DBF
If FileDateTime(PathStr & "Frc.dbf") The
DoCmd.TransferDatabase acImport, "Foxpro 3.0", PathStr, acTable, "Frc.dbf", "FRC
End I
Else 'comment this line
 
Hi Terry,

You haven't indicated the line at which the code fails or what error it
gives. Other comments inline.

Here is the code from the command button that we were using
the only changes I made was to the line PathStr = "C:\CAMSTEST\"
(this is the location on my pc for the trial data) . The
previous code was PathStr = "F:\DBVDATA\" which was the the
location on our old server. We are deleting three tables and
importing the updated data from another database into our
access database. I comment these lines out only when the
code ran once and deleted the data and did not complete
the process.

strMsg = MsgBox("Are you sure you want to import data?", vbOKCancel)
If strMsg = 1 Then 'comment this line
DoCmd.DeleteObject acTable, "FAS" 'comment this line
DoCmd.DeleteObject acTable, "FAT" 'comment this line
DoCmd.DeleteObject acTable, "FRC" 'comment this line

PathStr = "C:\CAMSTEST\"
PathStr = DLookup("[PathStr]", "tblPathStr")
Click to expand...

The two preceding lines don't make sense.

First you set PathStr to "C:\CAMSTEST\".

Immediately afterwards you use DLookup to set PathStr to the value of
the PathStr field in the first record in tblPathStr.

Which of these do you really want to do?
FileStr = "FAS.DBF"
Click to expand...

You assign "FAS.DBF" to FileStr - and then ignore it, using the literal
string
"Fas.dbf"
in the subsequent lines of code. What's the intention?
If FileDateTime(PathStr & "Fas.dbf") Then
Click to expand...

I've never before seen FileDateTime() used to test whether a file
exists. When I try it, it fails with error 53 if the file doesn't exist.
Much better to use
 
I get the error path not found. then the next box displays 76. The code deletes the tabes so it fails after the third table is deleted. This would be the string assinment for the path. I know that the path is correct

Thanks
Terry
 
Terry,

You have to help yourself. Open the VBA editor, find the code and set a
breakpoint at the beginning of the code in question, e.g. at

DoCmd.DeleteObject acTable, "FAS" 'comment this line

Then switch back to your database and click the command button. The code
will stop at the breakpoint. You can then use the Debug Toolbar to step
through the code one line at a time and check the value of PathStr at
each point. You will also discover exactly where the code is failing.
 
I ran a watch on the PathStr & FileStr. They both seem to change back to the origional location on the F:\ even though I assigned the one to C:\. I did a compact and repair and this did not change the situation. Do you have any suggestions

Thanks
Terry
 
Well, your code is retrieving PathStr from an unspecified record in
tblPathStr. Make sure that the table contains just one record, and that
the value in that record is the one you want.

(If tblPathStr contains more than one record you'll need to add a
condition to the DLookup() to ensure that it retrieves the value from
the correct record.)
 
Back
Top