-1

I am creating a database in Access 2013.

I need to create a few tables that are linked to a SQL Server that other Access queries will use to get data. No information will be returned to the SQL Server, the tables are for information only.

I tried using a pass-through query to SQL Server, which worked except that I couldn't refresh it, I had to go back to 'SQL View' and click 'Run'. I did some research and found that pass-through queries are not updateable. If someone could prove that wrong I would be glad!!

I then tried to create a linked table to SQL Server, however it only showed 22 columns (all of which happen to be totally useless to me) out of 267 columns (don't worry, I only need about 3 or 4 of those columns).

Is there a way to do this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mackles24
  • 111
  • 1
  • 8
  • 1
    267 columns in a single table? Sounds like some normalization should done soon. Aside from that your question is a bit confusing. Are you using a link table via a view in sql server? – Sean Lange Apr 06 '15 at 15:31
  • Yes, it contains most of the information about all of our parts. – Mackles24 Apr 06 '15 at 15:45
  • I tried creating a linked table in access to a table in our company's database on SQL server. Does that help or not? I'm not entirely sure what you mean. – Mackles24 Apr 06 '15 at 15:48
  • When you say pass through sql I assume you mean you are running a query against sql server? If that is the case, why do you need a link table? Can't you just create a connection to sql and run your query there? – Sean Lange Apr 06 '15 at 16:16
  • Yes, that is correct. – Mackles24 Apr 06 '15 at 16:23
  • I did, but then I cannot refresh it – Mackles24 Apr 06 '15 at 16:23
  • What do you mean you can't refresh it? – Sean Lange Apr 06 '15 at 16:24
  • I get an error that says it can't find the file, with the filename being the database table i specified in my 'FROM' statement – Mackles24 Apr 06 '15 at 16:29
  • File??? Are you making a connection to your sql server? It sounds like you are running an Access query. – Sean Lange Apr 06 '15 at 18:05
  • I still think that 267 columns is ridiculous, especially for a table of parts. It sounds like you need to have a number of additional tables to get your parts normalized. – Sean Lange Apr 06 '15 at 18:05
  • Exactly, I can't figure out why it's looking for a file. I have entered all the necessary info in the 'Connection String' field and it doesn't seem to be looking at that. – Mackles24 Apr 06 '15 at 18:34
  • Two things, I have no authority over how our database is set up (or how many columns it has) and whether it's ridiculous or not has nothing to do with my question. I only mentioned it in case there was some sort of limit on creating a linked table – Mackles24 Apr 06 '15 at 18:36
  • Offering some advice in the hope it would be met with an opportunity to learn better ways. I realize it had nothing to do with your question. Back to your current issue, can you share some code so I can see what you are trying to do? It seems like you are making a link table when querying sql directly is likely a better option. – Sean Lange Apr 06 '15 at 19:05
  • The sample SQL code I wrote when I was testing this out (I will be making it significantly more complex once I get this issue figured out) was `SELECT item.item, item.description FROM Syteline_MTL_PROD_App.dbo.item item ORDER BY item.item` "Syteline_MTL_PROD_App" is the database, "dbo.item" is the table and "item" and "description" are column names – Mackles24 Apr 06 '15 at 19:15
  • That's really weird, I am no longer getting any errors. I don't believe I have changed anything!? – Mackles24 Apr 06 '15 at 19:21
  • *"I tried using a pass-through query to SQL Server, which worked except that I couldn't refresh it, I had to go back to 'SQL View' and click 'Run'."* - That's odd, because [F5] (Refresh All) does in fact refresh (requery) a pass-through query. – Gord Thompson Apr 06 '15 at 19:24

1 Answers1

0

Sorry, I don't exactly know why I was getting errors, but I am no longer having problems with this.

Mackles24
  • 111
  • 1
  • 8