Accessing multiple tables in a single linked database - error

[DOlenchuk]

Hello,


I'm trying to run a query against 3 different tables in a single database on a linked (Access 97 MDB) server.

I'm attempting to run the query through VB.NET code in a Sub, called from a button click.

The query will error out, however, returning for each table (for however many instances of the table name exsist in the query):

Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'Model' does not match with a table name or alias name used in the query.

code as follows:

SELECT DISTINCT Model.TYPE as TYPE, Model.[MODEL_NO] as [MODEL_NO],
                Dealer.Dealer_ID, (Dealer.DNAME) AS DName, (Dealer.DADDRESS1)
                as Address1, (Dealer.DADDRESS2) as Address2, (Dealer.DTOWN)
                as Town, (Dealer.DContry) as Country, (Dealer.MANAGER)
                as Manager, Dealer.[TEL (OFFICE)] as OfficeTel, SerialNo.[SERIAL_NO], Model.[MODEL_ID] FROM CoinsTBL...Model
                INNER JOIN (CoinsTBL...Dealer INNER JOIN CoinsTBL...SerialNo ON
                Dealer.Dealer_ID = SerialNo.[Dealer_ID]) ON Model.[Model_ID] = SerialNo.[Model_ID]
                WHERE (((SerialNo.[SERIAL_NO])=6445));
 

I know the code is a mess, I've been fiddling with it for most of the day. I did not write the original code.

I can reduce the number of errors returned by removing table references in front of field names, but that also results in 'ambiguos field name' errors in the JOIN statements.

Any ideas why this won't work? If i strip it down into the component table queries, they all work, but it seems when you add them together, something breaks.

[code green]

The column prefix 'Model' does not match with a table name or alias name used in the query

This error is stating that there is no table with the name Model in your query.
That is you haven't included it in the FROM or as part of a JOIN.
I don't see the need for this prefix

CoinsTBL...

[DOlenchuk]

[code]
I don't see the need for this prefix

CoinsTBL...

that prefix is required for the SQl server to access the linked Access 97 .mdb file.

After more trial-and-error, I managed to get this code to work after prefixing all field names with "COINStbl."

eg: COINStbl.Model.Model_NO

it seems to properly access a linked server, you have to reference it using a full path name.

[code green]

Now this makes sense

COINStbl.Model.Model_NO

Database name>>tablename>>fieldname
and is a standard throughout most engines.
I did not recognise the treble '...' but guessed COINStbl was your database.

it seems to properly access a linked server, you have to reference it using a full path name

Surely your connection DB connection command has already established this path?

[DOlenchuk]

.
Surely your connection DB connection command has already established this path?

This is my Connection code in the VB code behind:

    ' Make a connection to the SQL Server
    Public Sub dbConnect()
        Try
            sConnectionString = ConfigurationSettings.AppSettings("ConnectionString") ' appSettings in web.config
            connectToCOPSTEST = New SqlConnection(sConnectionString)
 
            If connectToCOPSTEST.State = ConnectionState.Closed Then
                connectToCOPSTEST.Open()
                lblConnectionStatus.Text = connectToCOPSTEST.State.ToString
 
            End If
        Catch ex As Exception
            lblMessages.Text = ex.ToString()
        End Try
    End Sub
 

This is the HTML connection string:

<add key="ConnectionString"
    value="Data Source = ****; User Id=***; Password=***; Database=master; Timeout=200" />
 

It's pointing to the 'master' database, as thats where some stored procedures are for this database project. As far as I could tell, this is nessary due to the linked nature of COINStbl.

Please let me thank you for your help, I'm learning .NET as I go in this project, so its been... Internesting.