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

Michael-

Is the field VIEWER_01 in the Record Source of the report? You can't expect
the Picture property to interpolate. If VIEWER_01 is in the Record Source,
you might be able to do:

Me.ImageFrame.Picture = Me([DWG])

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 7:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Migrating Access Database with SQL backend to
new server

Here's the contents from Expression Builder when I pull up that expression:

DWG: VIEWER_01

The intent is that this expression is supposed to look up the path to a
bitmap of the drawing for the part being called for in a shop order (stored
in the "VIEWER_01" field of the "Part Master" table on our SQL database).
When a given report is run for the Access database, the underlying query is
supposed to return this path, and the report is supposed to print the bitmap
image on the last page of each shop order. Some report return one order,
others can return multiple orders, based on different criteria. The bitmaps
are stored in the same location (on a different server from either SQL
server), and the drive mapping for the network drives is the same for both
servers (e.g. the drawing bitmap for part number 12345 would be
H:\DRAWINGS\12345.bmp). Do I need to modify this expression so that the
path to the file (or at least the table (Part Master)) is spelled out?

Thanks again,

Michael

--- In MS_Access_Professionals@yahoogroups.com, John Viescas wrote:
>
> 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-B81
> > > > 6-
> > > > 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
>

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

Yahoo! Groups Links