Combining multiple tables using??? 2

[Homma]

Hi,

I have a one off task that I'm trying to do which is sendng me round the bend.

Background: Had 2,700 "dbf" files in a directory which I have extracted 1 table from each file into Access 2 (which has been converted into Access97). Each table has the same name (CustomerDetails) but has a numeric figure (from 1 - 2700) tagged on the end (ie CustomerDetails1). Each table has the same field names.

Issue: What I'm trying to achieve is to combine each of the 2700 tables (which has 1 row, 5 columns of data) into one master Access97 table.

Does anyone have any suggestions on how I can do this?

Many Thanks
Adam X-)

 

[bev]

I'm such a beginner and am working from memory with no books handy, so I'm sure this isn't quite right, but maybe it's close enough to get you headed in the right direction:

sOutName = CustomerDetails_All
For i = 1 to 2700
sName = "CustomerDetails" & char(i)
DoCmd.SQL "INSERT * INTO " & sOutName & " FROM " & sName
Do Next

Bev

 

[bev]

I'm such a beginner and am working from memory with no books handy, so I'm sure this isn't quite right, but maybe it's close enough to get you headed in the right direction:

sOutName = CustomerDetails_All
For i = 1 to 2700
sName = "CustomerDetails" & char(i)
DoCmd.SQL "INSERT * INTO " & sOutName & " FROM " & sName
Do Next

Bev

 

[Homma]

Bev,

Appreciate your time taken in replying. I'm too a beginner in Access'97 & find it frustrating when things don't come together.

I have tried to run the sample from a command button on a form but it returns an error message of "Object doesn't support this property or method". The INSERT line is fully highlighted. (prior to this error I received a Expected Array, but putting " " around "char (i)" eliminated this.

I have assumed that I do not need to state any of the field names in the code you supplied and running it from a command button is ok.

Any thoughts on what I am missing?

Adam X-)

 

[tlbroadbent]

Change the DoCmd line to use the RunSQL method. Also, change the INSERT syntax slightly. There is no CHAR function in Access. There is a CHR function but it is not applicable in this function. Use CStr instead.

sName = "CustomerDetails" & CStr(i)
DoCmd.RunSQL "INSERT INTO " & sOutName & "SELECT * FROM " & sName Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Homma]

G'Day Terry,

Thanks for yout time & input.

I have amended the code as below. When I run this (from a command button on a form), I get a "Syntax error in INSERT INTO statement". Any solution to this error message?

Private Sub Command2_Click()

sOutName = "ALL"
For i = 1 To 2706
sName = "CustomerDetails" & CStr(i)
DoCmd.RunSQL "INSERT INTO" & sOutName & "SELECT * FROM " & sName
Next

End Sub

Is using the INSERT method this straight forward?

Cheers
Adam X-)

 

[tlbroadbent]

Looks like your missing some required spaces. And yes, it really is quite simple and straight forward.

DoCmd.RunSQL "INSERT INTO " & sOutName & " SELECT * FROM " & sName & ";"

I add the & ";" on the end though it shouldn't be required. Access tends to add a semi-colon at the end of all SQL statements.
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.