0

Im new to VBA and Acces and Im trying to take the values from this array and put all the items in a table. To make it more difficult, I dont actually have access to the database. This is the code I have so far. I'm not sure it works. Im sorry for the lack of info and the way this is formatted. Stack wasnt playing nice for me.

Dim ii As Integer
Dim srtTarget As String
Dim aryValue As Varient
Dim rdsBoxTypes As Varient
Dim aryHeader As Object

Set rdsBoxTypes = dbADO.query("SELECT tblBoxTypes.* FROM tblBoxTypes LEFT JOIN tblSlipTypes ON [tblBoxTypes].[SlipID]=[tblSlipTypes].[SlipID] WHERE [tblSlipTypes.SlipID]=1")

For ii = 10 To 1 Step -1
    srtTarget = Replace(srtTarget, Space(ii), "Ø")
Next ii

aryValue = Split(strTarget, "Ø")

rdsBoxTypes.Filter = "[MatchString] ='" & aryHeader(ii) & "'"

If rdsBoxTypes.RecordCount <> 0 Then
    rdsValuesToReturn.RecordCount.AddNew
    rdsValuesToReturn.Fields("BoxValue").Value = aryValue
End If

Any suggestions and resources would be greatly appreciated!

Santosh
  • 12,175
  • 4
  • 41
  • 72
kaligirl
  • 51
  • 1
  • 9
  • _Im trying to take the values from this array and put all the items in a table._ It looks more like you're trying to read a table but it's not at all clear what you're trying to do with the values. – Nicholas Hunter Apr 21 '21 at 13:13
  • Id like to be able to take the values and make a table/recordset. I've been working on this way to long, but I don't know where to begin. I've never worked with VBA or access before this job – kaligirl Apr 21 '21 at 13:16
  • You can write the values to a different recordset using [Recordset.AddNew](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/recordset-addnew-method-dao) / Recordset.Update, or you can build a [SQL INSERT](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/insert-into-statement-microsoft-access-sql) statement and execute it using [Database.Execute](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/database-execute-method-dao). – Nicholas Hunter Apr 21 '21 at 13:20
  • Thanks Nicholas! I'll give that a try when I get confirmation on the code. – kaligirl Apr 21 '21 at 13:24
  • Did you mean to declare variable srtTarget as strTarget? – June7 Apr 21 '21 at 17:45
  • You don't know if the code works because you can't access the db? You are accessing the db - with code. Is that db an Access file? It is possible to use a query object to view data from that db. SQL example: `SELECT * FROM table IN "\\servername\pathhere\dbName.accdb";`. This way you can verify the results of your VBA. – June7 Apr 21 '21 at 17:59
  • @June7 that did help me! Even trying that SQL statement won't help me. Thank you for the help! – kaligirl Apr 21 '21 at 19:42
  • "did help" or "won't help"? – June7 Apr 21 '21 at 20:31
  • @June7 my bad, it did help! – kaligirl Apr 22 '21 at 12:36
  • And on second thought, only way your code could work is if you have links to tables in that db. If you have links then you certainly have access to database. Linking as well as my suggestion require knowing folder location of db, in which case should be able to navigate to and open an Access db. So your declaration of not having access to db really doesn't make sense. – June7 Apr 22 '21 at 17:06

0 Answers0