converting MDB SQL to ADP T-SQL help 1

[psimon88]

Hello

I am trying to move some queries and reports from an MDB file in Access 2003 to an ADP file in Access 2003. I am not very familiar with T-SQL and would prefer not to recreate all of them for scratch.

For example, see the SQL from this Access query:

SELECT dbo_VM_Leads.lApplicationID, dbo_VM_Leads.strFirstName, dbo_VM_Leads.strLastName, dbo_CostsIncome.lCostsIncomeID
FROM dbo_CostsIncome INNER JOIN dbo_VM_Leads ON dbo_CostsIncome.ReferID = dbo_VM_Leads.strReferId;

When I wrote this same query for the ADP file, it came across as:

SELECT dbo.VM_Leads.lApplicationID, dbo.VM_Leads.strFirstName, dbo.VM_Leads.strLastName, dbo.CostsIncome.lCostsIncomeID
FROM dbo.VM_Leads INNER JOIN
dbo.CostsIncome ON dbo.VM_Leads.strReferId = dbo.CostsIncome.ReferID

I see two diferences: Access uses underscores for MDP files (and periods for ADP files) and the spacing is off.

Can anyone help me figure out any other differences to expedite my conversion of queries?

Thanks

ps

 

[dhookom]

Aren't your results the same? Access doesn't understand the dot notation of sql server so it substitutes and underscore for the dots. An ADP should correctly use the dots which identify:
database.owner.objectname
Notice your tables/views are owned by dbo (database owner).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[AlexMidd]

Don't worry about the spacing.

Have fun! )

Alex Middleton