Import SQL > Table Question 1

[Pilly170]

Hi,

Can anyone point me in the right direction.

I want to write a function that imports 2 fields from a linked SQL table. fields =(ProductNumber, Price) and import it to a access table but insert a field "customer number" which is defined in the Function header eg Function xyz (custno as string) to every record returned. so the table would have customer number , product number, price for every record.

Ive been searching but cant find anything thats suitable. I was hoping someone could nudge me in the right direction.

 

[M8KWR]

I'm a bit unsure about the customer number bit, but hoping you an explain a little further.

But to create a new table off a linked sql table - create a make table query. Insert the fields you require.

Obviously naming the new table as you wish, and then running the query.

If you can let me know a little bit more information about the customer number and how you are generating this i can offer you further assistance in your issue

 

[Pilly170]

I was going to specify it when calling it

eg a macro
Runcode
FindPrice_Customer ("14427")

Where 14427 is variable for the customer

I can create the table with the required schema, but i need to add the customer number into every record. so for example the fields are custno,prodno, price, with values of 14427,15500,2.99
next record
14427,66332,8.99

does that help?

 

[M8KWR]

if the customer number is a static number then within your query in the design mode (not the sql code) put this into a column header

Customer Number:"14427" - and 14427 will appear in this row for each record...

Hope that helps...

 

[Pilly170]

This is what i have at the moment as a test

Function Update_XML_Tables(Custno As String, Table As String)

Dim XMLupdate As String

XMLupdate = "INSERT INTO XML_Upload_Pricelist ([CustNo], [ProdNo], [Price])Values ('" & Custno & "','Green',2.99)"
DoCmd.SetWarnings warningsoff
DoCmd.RunSQL XMLupdate
DoCmd.SetWarnings warningson


End Function

Now this inserts ok into the table, now i would like to insert for every record of a linked table specified in the function title. Is this possible?

 

[Golom]

What's unclear here is what you expect to see in the [CustNo] field for each record. You can just

XMLupdate = _
"INSERT INTO XML_Upload_Pricelist ([CustNo], [ProdNo], [Price]) " & _
"Select '" &  CustNo & "' As CustNo, ProductNumber, Price " & _
"From LinkedTable "

The problem is that every record will have the [CustNo] contained in the "CustNo" variable.

 

[Pilly170]

Thats right. then when i move onto the next table, it will do the same. There for i will have 1 table with all the information I need, customer, product, price.

for example the table inserting to will contain:
Custno Product price

10076 12235 2.99
10076 15547 8.99
10076 56585 5.25
14427 12235 3.06
14427 15547 9.12
14427 56585 5.11

 

[Pilly170]

Think i have it working after the previous help

Function Update_XML_Tables(Custno As String, Table As String)

Dim XMLupdate As String

XMLupdate = "INSERT INTO XML_Upload_Pricelist ([CustNo], [ProdNo], [Price]) " & _
"Select '" & Custno & "' As CustNo, Actebis_ProdNo, Price " & _
"From " & Table & " "

DoCmd.SetWarnings warningsoff
DoCmd.RunSQL XMLupdate
DoCmd.SetWarnings warningson

End Function