ACCESS FEED: RE: [MS_AccessPros] Migrating Access Database with SQL backend to new server

Michael-

In your code, you're trying to do:

Me![ImageFrame].Picture = Me![DWG]

What's in Me!DWG ?? If ImageFrame is an Image control, it expects a string
that's the path to the item.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mbentfeld
Sent: Tuesday, January 29, 2013 5:38 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Migrating Access Database with SQL backend to
new server

Everyone,

Thanks again for your help. Sorry for the tardy reply but I've been swamped
with other issues lately. I made another test copy of the database in
question and re-ran the linked table manager (while logged in as the network
admin). The links were succesfully refreshed this time. (?!?). However,
I'm still getting the type mismatch error. I've uploaded two files
regarding the message to the group in the "Assistance Needed" folder (one
with just the text and the other the vba debugger screenshot). As near as I
can tell from the message, the problem seems to stem from a part of the
queries that generates each of the various shop order reports.
Specifically, each query accesses a picture stored in our Part Master table
on our SQL database (tied to our MRP system (Exact MAX)), hence the "DWG" on
the right side of the expression in the error files (DWG: VIEWER_01 is the
wording of the expression in the query design view). My guess is the
expression needs to be modified somehow, as refreshing the table links
caused this element to no longer be recognized. The question is how?

Thanks again to everyone,

Michael

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" wrote:
>
> Michael
>
> No, paste it into a standard module in your access database. It's written
in Access VBA. Be sure to add the ADO library. Code window >
Tools>References>Microsoft ADO Ext.2.x for DDL and Security.
>
> Also add the Microsoft ActiveX Data Objects 2.x library if it isn't
already checked.
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com, "mbentfeld" wrote:
> >
> > Hello Bill,
> >
> > Am I correct in assuming that I will have modify this in order to suit
my particular database, or can I use it as is? I tried dropping it into
Visual Basic Express 2010 and it came up with a half dozen errors.
> >
> > For example, the following came up as undefined:
> > Dim cnn As ADODB.Connection
> > > Dim cat As ADOX.Catalog
> > > Dim tbl As ADOX.Table
> >
> > Sorry, but coding was never my strong suit.
> >
> > Thanks again,
> >
> > Michael
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" wrote:
> > >
> > > Michael - use this code to change the table names in the tables
section. You don't have change them anywhere else if you rename them to what
they were. The tables can be named whatever you want on the Access side.
> > >
> > >
> > >
> > > Public Function dev_TbrRenameDBOTables() 'MsgBox "commented out"
> > > 'Purpose : Remove SQL Server "dbo_" from all table names.
> > > 'DateTime : 10/28/2000 15:17
> > > 'Author : Bill Mosca
> > > Dim cnn As ADODB.Connection
> > > Dim cat As ADOX.Catalog
> > > Dim tbl As ADOX.Table
> > > Dim strPrefix As String
> > >
> > > On Error GoTo err_RenameDBOTables
> > >
> > > strPrefix = "dbo_"
> > > Set cnn = CurrentProject.Connection
> > > Set cat = New ADOX.Catalog
> > > cat.ActiveConnection = cnn
> > > For Each tbl In cat.Tables
> > > If tbl.Name Like strPrefix & "*" Then
> > > tbl.Name = Mid(tbl.Name, Len(strPrefix) + 1)
> > > End If
> > > Next
> > >
> > > Application.RefreshDatabaseWindow
> > > MsgBox "Tables successfully renamed.", vbInformation, "dbo_
Dropped"
> > >
> > > exit_RenameDBOTables:
> > > On Error Resume Next
> > > Set cat = Nothing
> > > Set cnn = Nothing
> > > Exit Function
> > >
> > > err_RenameDBOTables:
> > > Select Case Err.Number
> > >
> > > Case Else
> > > MsgBox "An error (" & Err.Description & ") occurred in
RenameDBOTables.", vbCritical, "Error!"
> > > End Select
> > >
> > > Resume exit_RenameDBOTables
> > >
> > >
> > > End Function
> > >
> > > Regards,
> > > Bill Mosca, Founder - MS_Access_Professionals
> > > http://www.thatlldoit.com Microsoft Office Access MVP
> > > https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-
> > > 64270730881E
> > > My nothing-to-do-with-Access blog
> > > http://wrmosca.wordpress.com
> > >
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "mbentfeld" wrote:
> > > >
> > > > John,
> > > >
> > > > I'm not sure what the error is pointing to. I'll re-upload the file
per your suggestion.
> > > >
> > > > When I went to re-link the SQL tables after deleting them, I did
> > > > find that the names had changed (e.g. what was "Part Master" is
> > > > now "dbo_Part_Master". This led to "could not find the
> > > > object..." errors in the queries, as they were unable to find
> > > > the necessary tables. Therefore, I'm going into the code of the
> > > > queries and changing the table names manually (I keep getting
> > > > errors that it can't be done in Design View.)
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas wrote:
> > > > >
> > > > > Michael-
> > > > >
> > > > > Does the error point to code, or is it something in the
> > > > > report? I can't find your uploaded file - you should put it in
Files / 2_AssistanceNeeded.
> > > > >
> > > > > Try deleting and relinking the tables before you do anything
> > > > > else. This is often necessary if you have moved the tables
somewhere else.
> > > > >
> > > > > John Viescas, Author
> > > > > Microsoft Access 2010 Inside Out Microsoft Access 2007 Inside
> > > > > Out Microsoft Access 2003 Inside Out Building Microsoft Access
> > > > > Applications SQL Queries for Mere Mortals
> > > > > http://www.viescas.com/ (Paris, France)
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > -----Original Message-----
> > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of
> > > > > mbentfeld
> > > > > Sent: Friday, January 25, 2013 3:38 PM
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > Subject: Re: [MS_AccessPros] Migrating Access Database with
> > > > > SQL backend to new server
> > > > >
> > > > > John,
> > > > >
> > > > > Thanks for the response. Maybe that will be necessary. The
> > > > > odd thing is that when I tried logging into the test station
> > > > > using the domain administrator credentials, I could get past
> > > > > that step, sort of. When I tried running one of the queries,
> > > > > off the switchboard form, I got a different error. The
> > > > > reports generated by these queries run two pages per order.
> > > > > The first page is the order itself (part to be made,
> > > > > components/materials required, etc.) The second page contains
> > > > > a bitmap copy of the drawing for the part specified in the
> > > > > order (stored on a separate server which won't change). I
> > > > > then get a runtime error code 13 (type mismatch). I'll upload a
scanned copy of the vba debugger screenshot with the particulars.
> > > > >
> > > > > Thanks again,
> > > > >
> > > > > Michael
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------
> > > > >
> > > > >
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com, John Viescas
wrote:
> > > > > >
> > > > > > Dear mbentfeld- (name?)
> > > > > >
> > > > > > Try deleting all the linked tables and relinking them from
scratch.
> > > > > >
> > > > > > John Viescas, Author
> > > > > > Microsoft Access 2010 Inside Out Microsoft Access 2007
> > > > > > Inside Out Microsoft Access 2003 Inside Out Building
> > > > > > Microsoft Access Applications SQL Queries for Mere Mortals
> > > > > > http://www.viescas.com/ (Paris, France)
> > > > > >
> > > > > >
> > > > > >
> > > > > > -----Original Message-----
> > > > > > From: MS_Access_Professionals@yahoogroups.com
> > > > > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf
> > > > > > Of mbentfeld
> > > > > > Sent: Friday, January 25, 2013 2:28 PM
> > > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > > Subject: [MS_AccessPros] Migrating Access Database with SQL
> > > > > > backend to new server
> > > > > >
> > > > > > Hello all,
> > > > > >
> > > > > > My company uses an Access database (originally created in
> > > > > > Access 2000) to generate shop orders for manufacturing
> > > > > > parts. This database ties into a SQL Server database called
> > > > > > RAYCO5 that ties into our MAX 5.0 MRP system. We are in the
> > > > > > process of migrating these databases to a new server. Both
> > > > > > old and new servers run SQL Server 2008, the only difference
> > > > > > being the new server runs Windows Server 2008 R2 x64 (old
> > > > > > runs 2008 Standard 64-bit). Both servers have been running
> > > > > > in parallel
> > > > > for about a week to synchronize data.
> > > > > > We've had a select group of users running MAX tied to the
> > > > > > new server through a test workstation with no problems, but
> > > > > > I can't get this Access database to link up correctly. I
> > > > > > made a copy of the database, and pointed the DSN to the new
> > > > > > server (old and new are using the same DSN name) through the
> > > > > > ODBC settings on Windows Control Panel. When I go to
> > > > > > refresh the links to the linked SQL tables through Linked
> > > > > > Table
> > > > > Manager, I get the following message:
> > > > > >
> > > > > > "The Microsoft Office Access database engine could not find
> > > > > > the object 'Job Progress.' Make sure the object exists and
> > > > > > that you spell its name and the path name correctly."
> > > > > >
> > > > > > I've looked at the database through SQL Server Management
> > > > > > Studio and the table is indeed there on both servers. Why
> > > > > > can't the Access database see the SQL table on the new server?
> > > > > >
> > > > > > Any thoughts?
> > > > > >
> > > > > > Thanks in advance.
> > > > > >
> > > > > >
> > > > > >
> > > > > > ------------------------------------
> > > > > >
> > > > > > Yahoo! Groups Links
> > > > > >
> > > > >
> > > >
> > >
> >
>

------------------------------------

Yahoo! Groups Links