December 13, 2016 at 1:38 pm
Hi! Just a little background on this issue. So we have this Access .mdb file that HR uses to pull pension information from Sage 100 Contractor. The latter was running v19.7 and Visual FoxPro before we upgraded to the newer version v20.x which now runs with SQL Server as its database. Now that we are using SQL server, I'm having trouble making that access file to pull information to the new database.
The specific error I'm getting when I'm trying to run anything is: The "Access file" could not find actrec object. This object is an actrec.dbf in the old database (Visual FoxPro). Is there any way to fix this?
/edit: Below is the code I'm trying to edit. I'm not a pro at VBA but I can edit them upon research.
Option Compare Database
Private Sub cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click
Dim dbs As Database
Dim tbl As Recordset
Dim count As Integer
Dim intNum As Integer
Set dbs = CurrentDb
Set tbl = dbs.OpenRecordset("tblRefresh", dbOpenDynaset)
Dim stconnect As String
stconnect = "ODBC;DRIVER=SQL Server;DSN=Sage 100 Contractor SQL;Database={database_name};Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;;TABLE="
Dim stDocName As String
varReturn = SysCmd(acSysCmdSetStatus, "Importing")
intNum = tbl.RecordCount
count = 1
tbl.MoveFirst
Do Until tbl.EOF
stDocName = tbl!Table
'delete current table
DoCmd.DeleteObject acTable, stDocName
'import new table
stconnect = stconnect & stDocName
DoCmd.TransferDatabase acImport, "ODBC Database", stconnect, acTable, stDocName, stDocName
count = count + 1
'SysCmd(acSysCmdUpdateMeter, "Importing") = count
tbl.MoveNext
Loop
SysCmd (acSysCmdClearStatus)
MsgBox "Master Builder Tables have been refreshed."
Exit_cmdRefresh_Click:
tbl.Close
Set dbs = Nothing
Exit Sub
Err_cmdRefresh_Click:
MsgBox Err.Description
Resume Exit_cmdRefresh_Click
End Sub
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click
DoCmd.Close
Exit_cmdExit_Click:
Exit Sub
Err_cmdExit_Click:
MsgBox Err.Description
Resume Exit_cmdExit_Click
End Sub
Private Sub Detail_Click()
Dim frm As String
Dim twd As String
frm = "{company database path}"
twd = "{company database backup path}"
End Sub
December 14, 2016 at 3:03 pm
Since you are now trying to connect to SQL Server, you will likely need to setup an ODBC Data Source on the system in HR - has that been done? If so, you should be able to link to the SQL Server table that contains the data you are after, although that presumes they kept the schema fairly similar to what you had in FoxPro. Then is is simply a matter of running a make-table query to copy the data you are after. Doing that would not require any VBA.
One other issue you may encounter is SQL Server security. Your userID will either need to be granted read permissions, and the process for doing that depends on whether the Sage app is using Standard Security or Integrated Security. Post back if you encounter problems with the above approach.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
December 14, 2016 at 3:45 pm
I'm not sure if I'm doing it right though. I've linked the SQL database to Access. I've done it with a valid user (connection test was successful). What bugs me is whenever I try to run the program, it still references to the Visual FoxPro. Like in Visual FoxPro, the tables are separate .dbf files (right?) but in SQL server, tables are now under 1 database umbrella which is only 1 .mdf file.
/edit: Also, how do I check the currentdb set? There is this part of the code:
Set dbs = CurrentDb
Set tbl = dbs.OpenRecordset("SELECT * from tblImport ORDER BY jobnum", dbOpenDynaset)
Set rst = dbs.OpenRecordset("tblABCOveride", dbOpenDynaset)
I want to know what is the database being pointed to by the CurrentDb code.
December 14, 2016 at 7:46 pm
What I suggested, perhaps obliquely, is that you don't need to run code to do what you are trying to do if you have the table actually linked into your current database. But to answer your questions:
You are correct - SQL Server (like Access) stores all the tables in one file, or depending on the design, in multiple files if the database is distributed or very large.
CurrentDb refers to the current ACCESS database, not to FoxPro or SQL Server.
[code}Set tbl = dbs.OpenRecordset("SELECT * from tblImport ORDER BY jobnum", dbOpenDynaset)
Set rst = dbs.OpenRecordset("tblABCOveride", dbOpenDynaset)[/code]
Those statements are simply opening tables that Access knows about - they are either local Access tables or linked tables from SQL Server or FoxPro or....
The VBA code you posted is essentially counting the records in the external table using DAO - not very efficient - and then deleting the existing Access table and doing an import to replace the table. I don't see any reference to the actrec.dbf in your VBA, so I suspect it is a linked FoxPro table in your Access database that cannot be located, but that is strictly a guess.
Without a good deal more information about your table structures and the version of Access and file type (.mdb or .accdb), we won't be able to provide much more assistance. EDIT - I missed your mention of the file type in your title - so we know you are working with a .mdb format database.
Wendell
Colorful Colorado
You can't see the view if you don't climb the mountain!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply